Transferring data between Microsoft SQL Server instances or even between databases in the same instance presents some constraints when the table schema on each database don’t exactly match.
There are scenarios where we can workaround these limitations by using the format files supported by the bcp utility.
For the first scenario, as you can see in the following picture, we try to import data to a table similar to the source table, but with an extra column. Even though the new column can accept NULL values importing the data to this table will fail if a format file is not used.
Import operation fails (zero rows imported):
bcp export.dbo.Contact out data.dat -n -S localhost -T bcp import.dbo.Contact in data.dat -n -S localhost -T pause
Import operation succeeds:
bcp export.dbo.Contact format nul -f Contact.ff -n -S localhost -T bcp export.dbo.Contact out data.dat -f Contact.ff -S localhost -T bcp import.dbo.Contact in data.dat -f Contact.ff -S localhost -T pause
In the previous scenario the extra column was at the end of the table meaning that all remaining columns were in the same order in both tables. Another scenario is when both source and target tables have matching columns, but their order is different as illustrated in the next figure. This case can also be resolved using format files, but now we need to edit the file before the import operation to correctly map column positions.
Using the format file generated against the source table without modifications may result in errors during the import or even worse because if columns data types are compatible the import operation could succeed but the data would be stored in the wrong columns.
Import operation succeeds but data for Fax column is loaded into OtherPhone and vice-versa:
bcp export.dbo.Contact format nul -f Contact-exp.ff -n -S localhost -T bcp export.dbo.Contact out data.dat -f Contact-exp.ff -S localhost -T bcp import.dbo.Contact in data.dat -f Contact-exp.ff -S localhost -T pause
To fix this issue we need to update the format file section representing the column order of the table. Doing this manually for several tables would be tedious and time consuming so I implemented a small utility (Format File Mapper) that given the format file associated to the source table and the format file associated to the target table will generate a mapped format file with the column’s order updated.
Import operation succeeds:
bcp export.dbo.Contact format nul -f Contact-exp.ff -n -S localhost -T bcp export.dbo.Contact out data.dat -f Contact-exp.ff -S localhost -T bcp import.dbo.Contact format nul -f Contact-imp.ff -n -S localhost -T ffmapper -s Contact-exp.ff -t Contact-imp.ff -o Contact-mapped.ff bcp import.dbo.Contact in data.dat -f Contact.Mapped.ff -S localhost -T pause
This utility only supports non-XML format files and was only tested against format files generated by SQL Server 2005. However, from what I’ve seen in MSDN, SQL Server 2008 creates non-XML format files with the same structure so it should also work.
There are of course other technologies better suited for data migration scenarios but for simpler cases and also for the command-line lover this can prove to be a very useful solution.