Exporting and Importing Data Using bcp Utility

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 to a table with an extra column
Import to a table with an extra column

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.

Import to table with columns in different order
Import to table with columns in different order

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.

Advertisements

3 thoughts on “Exporting and Importing Data Using bcp Utility”

  1. i am unable to find the ffmapper? where can i find it. Or please let me know how to do that mapping so i can implement it

    1. The host site for the link went down, I’ll upload it somewhere else as soon as possible. Meanwhile as you already did, the format files can be manually edited as a workaround.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s