BULK INSERT with identity (auto-increment) column

bulkinsert, identity-column, sql-server

Solution

Don't BULK INSERT into your real tables directly.

I would always

- insert into a staging table `dbo.Employee_Staging` (without the `IDENTITY` column) from the CSV file

- possibly edit / clean up / manipulate your imported data

and then copy the data across to the real table with a T-SQL statement like:

INSERT INTO dbo.Employee(Name, Address) 
   SELECT Name, Address
   FROM dbo.Employee_Staging

Problem

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).

Original source