BULK INSERT with identity (auto-increment) column

Question

I am trying to add bulk data in database from CSV file.

Employee table has a column ID (PK) auto-incremented.

CREATE TABLE [dbo].[Employee](  [id] [int] IDENTITY(1,1) NOT NULL,  [Name] [varchar](50) NULL,  [Address] [varchar](50) NULL ) ON [PRIMARY] 

I am using this query:

BULK INSERT Employee  FROM 'path\tempFile.csv '  WITH (FIRSTROW = 2,KEEPIDENTITY,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n'); 

.CSV File -

Name,Address name1,addr test 1 name2,addr test 2 

but it results in this error message:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (id).

Answer 1

Add an id column to the csv file and leave it blank:

id,Name,Address ,name1,addr test 1 ,name2,addr test 2 

Remove KEEPIDENTITY keyword from query:

BULK INSERT Employee  FROM 'path\tempFile.csv '  WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n'); 

The id identity field will be auto-incremented.

If you assign values to the id field in the csv, they'll be ignored unless you use the KEEPIDENTITY keyword, then they'll be used instead of auto-increment.