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:
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:
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.
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.