Let’s say you have a bunch of data stored in a table and you need to generate an insert script for each record. Maybe you need to save the scripts in source control or maybe you need the scripts to initialize the same table in another environment. What would you do?
Traditional Methods
Several ideas come to mind, all painfully tedious:
- You could dynamically create the insert statements with a stored procedure
If you do a quick search you’ll find several stored procedures that will generate insert scripts. This isn’t a bad way to go in terms of effort but I would definitely scrutinize the procedure before using it. And as you’ll see in a bit, there’s an even better approach.
- You could dynamically create the insert statements with a SQL query
Following this approach your SQL query would look something like this:
SELECT 'INSERT INTO dbo.MyTable (MyCol1, MyCol2) VALUES (' + MyCol1 + ',' + MyCol2 + ')' AS InsertStatement FROM dbo.MyTable
If your table is very wide then the script can be very complex to write and difficult to read and maintain.
- You could dynamically create the insert statements with Excel
This is a two-step approach where first you select all data from the table and then copy it into Excel. You then add columns before and after each value so you can create an expression for the insert statement. For example, cell A1 would look something like this:
=INSERT INTO dbo.MyTable (MyCol1, MyCol2) VALUES (
Using the fill handle you drag the expression down for each row and repeat for each of the other columns (remember, each value you insert needs to be separated by a comma and if you have character data then it must also be enclosed in single quotes). Once you copy your script out of Excel you will notice that the values in each column are separated by a tab. If you are inserting character data you will need to either update your expression and surround it with the RTRIM() and LTRIM() functions, or manually remove the tab yourself. This can get very ugly very quickly.
- You could manually type the insert statements
When all else fails you can always start to write the script yourself. Depending on how many rows of data you have, and the width of the table, this would require a tremendous amount of effort,
- You could convince someone else to generate the scripts for you
A-la Tom Sawyer and the fence, your mileage may vary with this approach.
A Better Way
Luckily, there is a far easier approach where SQL Server does all the work. Unfortunately, if you’re running SQL Server 2005 or earlier you’re still limited to the approaches mentioned above – this well-hidden gem is only available in SQL Server 2008 and later.
Starting in SQL Server Management Studio, navigate to the database where your data lives in the Object Explorer. Right click on the database name, go to Tasks, and select Generate Scripts.
The SQL Server Scripts Wizard will appear, click Next to begin.
Select the database where your data lives. In this example I am choosing AdventureWorksDW2008.
In the Choose Script Options there are a number properties that you can tweak. This is where the magic happens! In order to generate insert scripts you need to set the “Script Data” property located in the “Table/View Options” group to True (by default it’s set to False).
Select the type of objects you want scripted. Since we want to generate insert scripts the object type table nust be selected.
Select the tables for which you want to generate insert scripts. Here I’ve chosen DimAccount.
Select your output option.
Review your settings. Notice that “Script Data” is set to True.
Let the wizard spin…
And voila! An insert statement for each record (along with the table definition) has been generated!
So Now What?
I *highly* recommend that you check out and familiarize yourself with all of the options in the Check Options screen as it contains numerous ways to customize your scripts that could end up saving you a lot of time!