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:
- 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)
- Using the format-file I exported the data
- 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.
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.
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.