Generating INSERT statements in SQL Server

01/27/09

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:

InsertGenerator

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: webmaster@truewill.net
Copyright © 2000-2013 by William Sorensen. All rights reserved.