Sql-server – BCP did not preserve relational data

bcpsql server

I developed a document tracking database and front end C#/.NET app on my local machine (SQLServer Express and VS Express). Once it was working and usable I had our IT branch install the database (SQL Server Express) on the main server, and pointed the app to it instead of my local version. I then used bcp to export from my box and import to the server. I noticed that the parent child relationships are now all broken. Each table has an identity column called UID, which is referenced in child tables. But the UIDs did not copy as they were on my machine, rather they were all recreated upon insert. I have not found any documentation on the proper way to handle this, but now the data is basically unusable. I can easily redo the BCP, but can anyone point me to my mistake?

Here is what I used for the export (both the data and format just in case):

bcp DocControl.dbo.Documents out Documents.dat -n -S MYMACHINE\SQLEXPRESS -T
bcp DocControl.dbo.Documents format nul -T -n -f Documents.fmt -S MYMACHINE\SQLEXPRESS

And for the import (also, before import I had duplicated the schema, so the tables, keys and stored procedures were all recreated ahead of time, but without the data):

bcp DocControl.dbo.Documents in Documents.dat -n -S SERVER\SQLEXPRESS -T

This is just for one of the tables, but the other tables were done exactly the same way. Now the Documents table UID starts at 1 and goes up, whereas previously it started at something like 380 and went up from there. So the referring IDs in child tables no longer point to the right parent.

Best Answer

You're lacking the -E flag for identity columns, which allows you to preserve the values instead of re-seeding them on the import.

bcp DocControl.dbo.Documents in Documents.dat -n -E -S SERVER\SQLEXPRESS -T

From MSDN,

[-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, and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation.

If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view should be skipped when importing data; SQL Server automatically assigns unique values for the column.

Related Question