22 Oct 2014

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
#mssql #sql #bcp #import #export

Read more »