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