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

回答1:

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
   

回答2:

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.

回答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');

回答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

回答5:

My solution is to add the ID field as the LAST field in the table, thus bulk insert ignores it and it gets automatic values. Clean and simple ...

For instance, if inserting into a temp table:

CREATE TABLE #TempTable 
(field1 varchar(max), field2 varchar(max), ... 
ROW_ID int IDENTITY(1,1) NOT NULL)

Note that the ROW_ID field MUST always be specified as LAST field!

回答6:

Another option, if you're using temporary tables instead of staging tables, could be to create the temporary table as your import expects, then add the identity column after the import.

So your sql does something like this:

1. If temp table exists, drop
2. Create temp table
3. Bulk Import to temp table
4. Alter temp table add identity
5. < whatever you want to do with the data >
6. Drop temp table

Still not very clean, but it's another option... might have to get locks to be safe, too.

回答7:

I had this exact same problem which made loss hours so i'm inspired to share my findings and solutions that worked for me.

1. Use an excel file

This is the approach I adopted. Instead of using a csv file, I used an excel file (.xlsx) with content like below.

id  username   email                token website

    johndoe   [e-mail]        divostar.com
    bobstone  [e-mail]        divosays.com

Notice that the id column has no value.

Next, connect to your DB using Microsoft SQL Server Management Studio and right click on your database and select import data (submenu under task). Select Microsoft Excel as source. When you arrive at the stage called "Select Source Tables and Views", click edit mappings. For id column under destination, click on it and select ignore . Don't check Enable Identity insert unless you want to mantain ids incases where you are importing data from another database and would like to maintain the auto increment id of the source db. Proceed to finish and that's it. Your data will be imported smoothly.

2. Using CSV file

In your csv file, make sure your data is like below.

id,username,email,token,website
,johndoe,[e-mail],,divostar.com
,bobstone,[e-mail],,divosays.com

Run the query below:

BULK INSERT Metrics FROM 'D:\Data Management\Data\CSV2\Production Data 2004 - 2016.csv '
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

The problem with this approach is that the CSV should be in the DB server or some shared folder that the DB can have access to otherwise you may get error like "Cannot opened file. The operating system returned error code 21 (The device is not ready)".

If you are connecting to a remote database, then you can upload your CSV to a directory on that server and reference the path in bulk insert.

3. Using CSV file and Microsoft SQL Server Management Studio import option

Launch your import data like in the first approach. For source, select Flat file Source and browse for your CSV file. Make sure the right menu (General, Columns, Advanced, Preview) are ok. Make sure to set the right delimiter under columns menu (Column delimiter). Just like in the excel approach above, click edit mappings. For id column under destination, click on it and select ignore .

Proceed to finish and that's it. Your data will be imported smoothly.

回答8:

1. Create a table with Identity column + other columns;
2. Create a view over it and expose only the columns you will bulk insert;
3. BCP in the view