I am implementing SQL data migration from one database to another using C# application. The databases are on different units. The tables in both databases are mapped and have same columns, however for some tables in the target the column order is different from the source tables. While doing a 'BCP In' there appears errors due to this changed order of columns.
We cannot fetch the target table's column order while taking the backup.
Also, we cannot delete and recreate the table from the target.
Need help to restore the data in these tables with changed column order.
I searched over net and found '-n', or'-N' should not be used while BCP for such cases. However, in my case it didn't work and the BCP command was not working at all.
When using BULK INSERT (through the bcp utility) the objects schema definition need to match.
To solve this you could either change the column order in one table to match the other. This can be done via SSMS, link, and data will be persistent.
One other option is to create a view, which has the correct column order, and perform the bulk insert into the view. (Bulk insert supports view inserts) SQL Server will then perform the insert against the underlaying table in the correct order.