Exporting SQL Server Data to a Script

This post is more than 2 years old.

So, I recently ran into an interesting problem. I needed to export data from SQL Server, but I need it in a form that I could use in a script - in other words, I wanted to export to a series of insert statements. Unfortunately, SQL Server (specifically Enterprise Manager, and I should be clear that I use both terms off and on to refer to the same thing) only lets you export to a delimited file - not a file you can run as is. (Unlike mySQL-Front, a darn nice client for mySQL - it even lets you export to an HTML or XML file.)

So, I asked around for help and found a few solutions. The first solution was a script that would generate insert statements. This worked fine - except for the fact that Query Analzyer restricts the amount of data it can output. I can understand the reasoning behind this, but I think it is pretty darn stupid that it won't let me override this setting. (This isn't the only beef I have with Enterprise Manager though.) Since my data was pretty fat, this wasn't a solution.

The next thing I tried was a suggestion by Stephen Milligan. He said I could use the CSV file that SQL Server exports and then use the Bulk Copy Utility to import the data. This kinda worked - however I then had issues with the CSV data itself. First - all my strings were wrapped with quotes - not a big deal I thought - I'm sure BCP has a way to remove them. Unfortunately, I couldn't find a way to handle it. Then, all my bit fields were changed to True/False in the CSV file. So of course on reimporting this would throw an error. I'm sure this method would have worked for me if I had just tried a bit harder, but I simply didn't have the time.

So - I ended up using CFML. My code simply selects all the rows and then generates a file with insert statements. This file can then be run in Query Analyzer or via the command line.

Does anyone else have suggestions? I'm really surprised Enterprise Manager doesn't have a simple "Export the whole dang thing to a script" function. Sure you can backup/restore, but a script is a lot simpler - at least it was for me when I using mySQL (which I'm gaining a lot of respect for).

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate for HERE Technologies. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com