BCP – Collation woes

Spent a long time trying to get data from one instance of SQL Server to another with BCP. Couple of things I learnt along the way:

1. SQL_Latin1 _General_CP1_CI_AS v. Latin1 _General_CP1_CI_AS

May as well be Latin v. Russian as far as BCP is concerned. Was unable to import data from a Latin1 _General_CP1_CI_AS column into a SQL_Latin1 _General_CP1_CI_AS column. Banged my head against the wall for a good while on this, until eventually just manually set the collation on each column in the target database to match the source.

2. Identify columns

Because my identity values had a few gaps in in one table, the master-detail relationships became jumbled up, and the data was no longer consistent. Worked around this by inserting some dummy rows in the BCP files so that there was a row for every identity column.

Since I had deleted all of the rows in the target table after a failed attempt at importing, also needed to reset the seed. Which is straightforward:

DBCC CHECKIDENT (TableName, reseed, 0)

This means that when you use BCP to import, the first value will be 1, and so on.