What is the BCP format for inserting an identity column

Question

I ran into a problem while I try to insert data into a table using BCP. The table has an identifier column. I am taking data from a text file. Please let me know if there are any good solutions.

Regards, Chayan

Answer 1

You have two options:

• do not insert a value for the IDENTITY column and let SQL Server handle this for you

• If you cannot or do not want to do this, you need to enable IDENTITY_INSERT in this table, then insert the value and disable it again:

  SET IDENTITY_INSERT (table name) ON -- do your bcp import here SET IDENTITY_INSERT (table name) OFF 

  With this parameter, you can insert your own values ​​into the IDENTITY column.

  If you do this, you may also need to re-populate the identifier column after insertion to avoid possible duplicates in your IDENTITY:

   DBCC CHECKIDENT('table name', RESEED) 

Answer 2

I needed to do the same, and my colleague noted that you can use the -E switch on BCP for this.

From the documents ...

"- E Indicates that the identification value or values ​​in the imported data file should be used for the identity column. If -E is not specified, the identifier values ​​for this column in the imported data file are ignored."

It works, thanks Yang!

Answer 3

Creating a view, excluding the identifier column, is also useful; no format file is required:

 bcp mydb.dbo.myview in file.txt -S(local) -T -e err.log -c