What will be BCP format for inserting a identity column

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开发者_StackOverflow社区 solutions.

Regards, Chayan

I needed to do the same thing and my colleague pointed out that you can use the -E switch on BCP to do this.

From the docs...

"-E Specifies that identity value or values in the imported data file are to be used for the identity column. If -E is not given, the identity values for this column in the data file being imported are ignored."

Works a treat, thanks Jan!

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)
  

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

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