What will be BCP format for inserting a identity column

Question

Hi, I am facing problem while I am trying to insert data to a table using BCP. The table has a identity column. I am taking input from a text file. Please let me know if there are any good solutions.

Regards, Chayan

Answer 1

You have two options, really:

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

• if you cannot or don't want to do that, you need to turn on IDENTITY_INSERT on that table, then insert the value, and then turn it off again:

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

  With this setting, you're allowed to insert your own values into an IDENTITY column.

  If you do this, you might also need to reseed the identity column after the insert to avoid any potential duplicates in your IDENTITY:

  DBCC CHECKIDENT('table name', RESEED)