Generate SQL data INSERT statements from a table

If you've ever had to work with reference data across multiple instances of the same application, or have simply needed to script data contained in a table, then you'll know what I'm talking about here. I've needed to do this a few times recently, and finally managed to come across something which does exactly what I needed. Here's the blurb from the description, which sums it up better than I have here:

This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.

You simply run this code to place the stored procedure in your master database, and then run it from your query tool of choice supplying a table name as a paramater. The output is a SQL script which can be run to re-create the data elsewhere.

There were a couple of thing I'd tried before finding this solution. First, I'd checked out the SQL Red Gate Bundle. This tool is mostly used to keep database schemas in sync. Point it to 2 databases, and it will generate a script to make sure they match in terms of schema. Obviously they're meant to be of a similar schema in the first place. They also have a data compare tool, which will offer to sync data. It was along the right lines, but it didn't quite do what I needed. It's also a commercial product, so it's not something I'd want to build up much of a dependancy on just for this one function.

The other method I tried was generating a series of INSERT statements via SELECT statements. It's a much more manual method, requiring that you create and execute a query similar to this for each table:
select 'insert into tblPerson (FirstName) VALUES(''' + LTRIM(FirstName) + ''')' from tblPerson
This will perform a select on tblPerson, and generate a simple insert statement for each row in the table (set your results to text first). This is fine for a simple example, but it gets complex very quickly depending on how many columns you wish to preserve, the data typing of the columns, handling null values, as well as the fact that you'll need to cope with any escape characters that may be contained within your data. I've used more complex variants of the code above for other uses (such as taking a simple subset of data from a table, manipulating it slightly, and inserting it into another destination table), but the time it took to craft and check the code made it quite time consuming.

Chances are you've never needed something like this before. Maybe you never will either, but if you have, then check out this script. There's a lot of other code on the site too, the author (Narayana Vyas Kondreddi) looks like he's pretty skilled with SQL indeed. He's certainly saved me some time today, and is probably going to save me a lot more in the future.

Link: Procedure to script your data / Generate Inserts from existing data
Link: Direct link to the stored procedure code (2000 version)
Link: Direct link to the stored procedure code (2005 version)

Tags: SQL

 Print | Posted on Monday, February 12, 2007 10:28 PM | Filed Under [ Web Development SQL ]