How to Generate Insert Scripts for Existing Data

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!

This entry was posted in SQL Server, SSMS and tagged , , . Bookmark the permalink.

9 Responses to How to Generate Insert Scripts for Existing Data

  1. Sanjeev Kumar says:

    Nice post…Very much helpfull…thanx Melinda.

  2. Michael says:

    What about a way to script filtered data?

    Say we scripted all data initially, but now I want to go back and script over new data for the past 24 hours. I know the max(id) or even the max(create_date) and want to only script out data beyond that.

    Can Generate Scripts handle this? Thanks!

    • Melinda Cole says:

      Hi Michael,

      Unfortunately the Generate Scripts cannot handle scripting out partial data – it’s either all or nothing. However, with a little creativity you could get this to work by creating another table to store just the new data. Assume your base table is “dbo.MyTable” and you create a new table called “dbo.MyTableNewData”…


      --If the New Data table already exists, drop it
      IF EXISTS (
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'dbo.MyTableNewData'
      AND TABLE_SCHEMA = 'dbo'
      )
      DROP TABLE dbo.MyTableNewData
      GO

      --Select all of the new data from the Base table into the New Data table
      SELECT *
      INTO dbo.MyTableNewData
      FROM dbo.MyTable
      WHERE CreateDate >= DATEADD(D,-1,GETDATE())

  3. Lucas says:

    Thank you very much for the tutorial.

    Worth mentioning that the SQLServer 2008 R2 Express edition does not provide the option to “Script data”.

    However, I followed your second example and it works a treat.

    Thanks again….

    • Solly says:

      Hi

      Also useful approach since 2008 r2 doesnt give that option is under the “General” section there is an option for “Types of data to script”, here one can select schema only, data only or both schema and data :)

      Regards

      Solly

  4. Avinash Ahuja says:

    I saw this post very helpful for generating SQL Insert statements from an existing database table with options to filter and sort the output data. It works for IDENTITY columns also. Hope it may be useful to others as well.

    • Roji says:

      is there a good way to do aif exists ( sceeltStatementA )begin sceeltStatementAend?It seems really clumsy to repeat the same sceelt statement. or may be i’m not really supposed to use an if exists in this place o.ocan i haz help?

      • Melinda Cole says:

        Roji, that’s pretty much the only way to determine whether or not something already exists in your table. However, to improve performance, the query in your “IF EXISTS” statement can (should) be a simplified version of your main query.

Leave a Reply to Michael Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>