BULK INSERT with identity (auto-increment) column

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

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

Solution 2:

Don't BULK INSERT into your real tables directly.

I would always

1. insert into a staging table dbo.Employee_Staging (without the IDENTITY column) from the CSV file
2. possibly edit / clean up / manipulate your imported data

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

Solution 3:

I had a similar issue, but I needed to be sure that the order of the ID is aligning to the order in the source file. My solution is using a VIEW for the BULK INSERT:

Keep your table as it is and create this VIEW (select everything except the ID column)

CREATE VIEW [dbo].[VW_Employee]
AS
SELECT [Name], [Address]
FROM [dbo].[Employee];

Your BULK INSERT should then look like:

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

Solution 4:

You have to do bulk insert with format file:

   BULK INSERT Employee FROM 'path\tempFile.csv ' 
   WITH (FORMATFILE = 'path\tempFile.fmt');

where format file (tempFile.fmt) looks like this:

11.0
2
1 SQLCHAR 0 50 "\t"  2  Name   SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\r\n" 3  Address  SQL_Latin1_General_CP1_CI_AS

more details here - http://msdn.microsoft.com/en-us/library/ms179250.aspx