MSSQL Bulk Inserts

Oct 22, 2014

I recently had to export data from a table on one MSSQL database and import it into another ... a common and simple issue I guess.

The problem was that the amount of data was too much for simply using the SQL Server Management Studio (SSMS) data export guide and then opening the export in SSMS and running it as individual insert-statements — The export will work fine, but the import constantly failed due to an out of memory exception.

I then found the sqlcmd Utility, which supposedly should be a bit better at handling large file imports. I tried something like this, but ended up getting the same out of memory exception as when I imported using the SSMS:

sqlcmd -S <servername> -i C:\path\data-export.sql

Then I found the bcp Utility. It took me several tries and searches to figure out the following 3 steps (damn it Microsoft, you really suck at writing documentation!) — Anyways, what finally worked for me was to do the following:

1. I generated a format-file explaining the format for each column in the table I want to export and import (including field- and row-delimiters)
2. Using the format-file I exported the data
3. Again using the format-file I imported the data

Generate a format file

bcp <database>.<schema>.<table> format nul -U <username> -P <password> -f format.fmt -c -t: -r;

I ended up using the command as above, for generating the format file. I used the switches -c -t and -r ("format nul" is simply required, MS documentation says?!). The switches are as follows:

• -c: This option tells bcp to export using character format — This should be the ideal solution when exporting bulk-data to a text file.
• -t: This option is used for setting the field terminator, meaning the next character after the switch will be what determines a field ending — So don't pick a character that will be present in your field-data :p
• -r: This option is used for setting the row terminator, meaning the next character after the switch will be what determines that a row has been processed — Again don't pick a character that will be present in your field-data.
• -U and -P: This is simply the username and password used to connect to the database.
• -f: Whatever you want to name your format file.

Exporting data

bcp <database>.<schema>.<table> out data-export.txt -c -E -U <username> -P <password> -f format.fmt

Once I had the format file in place, I used this to export the data using the command above. This time I used a few other options.

• out: Simply the name of the file to which we want to export the data.
• -c: Again we wan't to perform the export using character format.
• -E: I needed to keep the identity for each row (Id's was important) — This flag will make sure they are taken into account.
• -f: The name of this format-file we generated before.
• -U and -P: Still the username and password for the database connection.

Importing data

bcp <database>.<schema>.<table> in data-export.txt -c -E -U <username> -P <password> -f format.fmt

Finally I could import the exported data using the command above ... options are the same as for export, except that we've changed the out-flag with an in-flag.