Search code examples
sql-serverbcp

BCP file insert command is not restoring the actual value of the Identity column

BCP insert script is not restoring the Identity column values copied in the BCP file, instead SQL Server automatically assigns unique values based on the seed and increment values specified during the restoring process.

BCP format file creation -

BCP [SourceDatabase].[abc].[TableName] format nul -T -c -f Filename.fmt -S SourceSqlServer

BCP Data file creation -

BCP "EXEC [abc].[GetData]" queryout "C:\Filename.bcp" -S SourceSqlServer -d SourceDatabase -T **-E** -c

Restore data into the table -

BCP [abc].[TableName] in "C:\Filename.bcp" -f Filename.fmt -S SourceSqlServer -d SourceDatabase -T

I want to insert the values of the Identity column same as in the BCP file. Any suggestions appreciated.

Solution

  • You will need to add the -E switch by default bcp assigns new identity columns.

    https://learn.microsoft.com/sr-latn-rs/sql/relational-databases/import-export/keep-identity-values-when-bulk-importing-data-sql-server?view=sql-server-2014