SQL Server – Generate the data script (insert script) for existing data in tables

How to generate the data script for SQL server 2008 database tables

Right click on the database where your table exists > Tasks > Generate Scripts

image

Click Next on the below screenimage

Choose “Select specific database objects” radio button, expand the tables and choose the table for which the data script needs to be generated and click Next.image

On the below screen, click “Advanced” and then select “Data only” option from the “Types of data to script”.
By default “Schema only” option is selected. You can also select “Schema and data” option for both creating table and also for the insert scripts.image

Choose where to save the insert script (File/Query window/Clipboard) and click on Next.You can see the progress and will see the generated insert script.
image

IMPORTANT NOTES  –
A) If your table has large amount of data (like mine in this case having 5000K records, Use the “Save to file” option to save the script and It will generate the script file containing all the insert queries just fine. If you use “copy to clipboad” or a “New Query Window option”, the generation will fail with Out of Memory Exception.

B) ALWAYS enclose the insert scripts in transaction with TRY CATCH block before running them. You don’t want partial inserts on your table – do you?

 

How to generate the data script for SQL server 2005 database tables

Good thing is you can generate the data script of SQL server 2005 database objects from SQL server 2008 management studio. This is how you do it.

a) In the SQL Management studio, right click on the database and select “Task > Generate Scripts ”. A wizard will be launched.
b)  Select the database you want to generate the script from.
c) In the “Choose Script option” step of the Wizard, select the “Script data” as true:-

clip_image002

d) Proceed on to generate the script and It will generate the data script as well.

Advertisements

,

  1. #1 by Dale Wilbanks on December 2, 2011 - 11:50 pm

    Sweet, thanks for sharing, I like this site, I could learn a lot reading through your articles.

  2. #2 by Pallavi Gupte on March 19, 2012 - 8:29 am

    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. It supports all datatypes available in SQL Server 2005.

  3. #3 by Vidrios on October 30, 2012 - 1:22 am

    in my sql server management studio, the only option under “TABLE/VIEW OPTIONS” is “Script Data Compression OPtions” and dont appear “Script data” option… somebody help me

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

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: