MSSQL Bulk Inserts

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.