Generating INSERT statements in SQL Server


Generating INSERT statements in SQL Server

For anyone who has ever used MySQL, SQL Server really seems to be missing the ability to export data as a series of INSERT statements.

A number of people have tried to remedy this. Here’s one I tried today:


This worked for what I needed. It seems to be limited to certain data types, and you do have to tweak Management Studio as noted in the comments: “select Tools, Options, select the Results tab then change the ‘Maximum characters per column:’ value to something bigger than 256″.

As for how I’m using it, I had a SQL Server Express file-based database set up as a test deck for some of our O/RM business objects. Some of the test methods change the data, and I wanted them to be independent. I set up the table data as I wanted it, ran the Insert Generator, then saved the INSERT statements to a stored procedure preceded with TRUNCATE TABLE. I then changed the SetUp test fixture to call the stored procedure.

I don’t like having to use a physical database to run the tests. With the way our O/RM persistence framework works, though, it’s difficult to avoid that.

I certainly wouldn’t recommend this stored procedure for production code. It worked fine for my test code, though.

There are a number of similar implementations. If anyone has a recommendation on the quintessential one, please post a comment!

Your Host:
Copyright © 2000-2013 by William Sorensen. All rights reserved.