Bulk Insert problem

Hello,

I am trying to insert thousands of records from a CSV file to my SQL Server database.
I am using the following to do the operation

BULK INSERT
CrimeStats.dbo.[TestDB]
FROM
'c:\Test.csv'
WITH
(
CHECK_CONSTRAINTS, FIELDTERMINATOR = ',', KEEPIDENTITY, KEEPNULLS, TABLOCK ,
ROWTERMINATOR = '\n'
)

It works fine.

My problem is I want my table in SQL server to have a Primary Key which would automatically increment. SO with every bulk insert that I would be doing every month, the Primary Key would automatically increment itself. The data in the CSV file does not have the ID field. So If I use the above script to upload the same data from the CSV file into my SQL table with the Primary Key, the BULK INSERT does not work.

It prints out the following error.

"Bulk insert data conversion error (type mismatch) for row 1, column 1 (ID)."

Is there any way that I could do what I am trying to do? The primary key is quite important in this table.

Any help on this would be much appreciated.

Thank YouSounds like you need an identity column in your table. TheBulk Insert topic in Books Online indicates that you need to specify a format file if you want SQL Server to automatically insert that identity column for you.

Terri