Home > SQL, SQL Server2008, Tools > Generate insert statements for existing data

Generate insert statements for existing data


Once in a while I get this query: Do you know any tool to generate scripts for data in an existing table, of course for SQL Server?

Well, There are quite a few options.

If your company is rich enough to sponsor a tool then you could go for Redgate SQLToolBelt. This has quite a nice set of tools to generate scripts from existing data or new data and to compare scripts etc. 

You could also try out the free tool :  http://www.ssmstoolspack.com But this works only with SQL server Management Studio 2005 SP2 or above. 

If you are using SQL Server 2008 you could use the built in option SSMS in 2008. This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

  1. DATABASE NAME: Right Click
  2. TASKS: GENERATE SCRIPTS
  3. Under Table/View Options: Set SCRIPT DATA = TRUE

You will then get the create table statement and all of the INSERT statements for the data straight out of SSMS.

You can also use this simple stored procedure given generously by Vyas.  The procedure that does the script generation is here.

I am using it for quite sometime now. Works perfect for my requirement!

Advertisements
  1. May 17, 2010 at 9:25 pm

    The problem with this approach that it is database specific plus you have to be connected to database.

    I had several situations when I wanted to generate inserts using text file but could not.

    Likely there is another solution
    http://www.dbsoftlab.com/Advanced-ETL-Processor-News/Generate-Insert-Statements.html

    Mike

  2. December 29, 2011 at 6:23 pm

    I am using SQL Server 2008 R2, and this procedure doesn’t work. I get the CREATE scripts but no INSERTS.

  3. Mike
    December 5, 2012 at 7:38 pm

    SQL 2008
    Options /General
    Types of data to Script / Schema and Data or Data Only (Whatever your doing)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: