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

User · Answer

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   [email protected]        divostar.com
    bobstone  [email protected]        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,[email protected],,divostar.com
,bobstone,[email protected],,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.

User · Answer

Create a table with Identity column   other columns  Create a view over it and expose only the columns you will bulk insert  BCP in the view

User · Answer

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     If temp table exists  drop Create temp table Bulk Import to temp table Alter temp table add identity  lt  whatever you want to do with the data   Drop temp table   Still not very clean  but it s another option    might have to get locks to be safe  too

User · Answer

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

User · Answer

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

User · Answer

This is a very old post to answer  but none of the answers given solves the problem without changing the posed conditions  which I can t do   I solved it by using the OPENROWSET variant of BULK INSERT  This uses the same format file and works in the same way  but it allows the data file be read with a SELECT statement   Create your table   CREATE TABLE target table  id bigint IDENTITY 1 1   col1 varchar 256  NULL  col2 varchar 256  NULL  col3 varchar 256  NULL    Open a command window an run   bcp dbname dbo target table format nul -c -x -f C  format file xml -t  -T   This creates the format file based on how the table looks  Now edit the format file and remove the entire rows where FIELD ID  1  and COLUMN SOURCE  1   since this does not exist in our data file  Also adjust terminators as may be needed for your data file    lt  xml version  1 0   gt   lt BCPFORMAT xmlns  http   schemas microsoft com sqlserver 2004 bulkload format  xmlns xsi  http   www w3 org 2001 XMLSchema-instance  gt    lt RECORD gt     lt FIELD ID  2  xsi type  CharTerm  TERMINATOR     MAX LENGTH  256  COLLATION  Finnish Swedish CI AS   gt     lt FIELD ID  3  xsi type  CharTerm  TERMINATOR     MAX LENGTH  256  COLLATION  Finnish Swedish CI AS   gt     lt FIELD ID  4  xsi type  CharTerm  TERMINATOR   r n  MAX LENGTH  256  COLLATION  Finnish Swedish CI AS   gt    lt  RECORD gt    lt ROW gt     lt COLUMN SOURCE  2  NAME  col1  xsi type  SQLVARYCHAR   gt     lt COLUMN SOURCE  3  NAME  col2  xsi type  SQLVARYCHAR   gt     lt COLUMN SOURCE  4  NAME  col3  xsi type  SQLVARYCHAR   gt    lt  ROW gt   lt  BCPFORMAT gt    Now we can bulk load the data file into our table with a select  thus having full controll over the columns  in this case by not inserting data into the identity column   INSERT INTO target table  col1 col2  col3  SELECT   FROM  openrowset  bulk  C  data file txt   formatfile  C  format file xml   as t

User · Answer

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 emsp SQLCHAR emsp 0 emsp 50 emsp   t  emsp  emsp 2 emsp  emsp Name emsp  emsp  emsp SQL Latin1 General CP1 CI AS   2 emsp SQLCHAR emsp 0 emsp 50 emsp   r n  emsp 3 emsp  emsp Address emsp  emsp SQL Latin1 General CP1 CI AS   more details here - http   msdn microsoft com en-us library ms179250 aspx

User · Answer

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

User · Answer

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

[sql-server] BULK INSERT with identity (auto-increment) column

Examples related to sql-server

Examples related to bulkinsert

Examples related to identity-column