sql server - 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 'pathempFile.csv ' 
WITH (FIRSTROW = 2,KEEPIDENTITY,FIELDTERMINATOR = ',' , ROWTERMINATOR = '
');

.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 'pathempFile.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '
');

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.