Creating Insert scripts in Sql Server 2008 R2

There were times when I wanted to experiment something in my project and needed a separate local DB with the real time data. It used to take lot of time to achieve it. but with Sql Server 2008 R2 management studio itself provides this feature handy.

This feature is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

Here are the steps to create Insert Data script using SQL Management Studio 2008 R2. You do not need any pugins or third party tools for this.

  1. Right Click on Database name like ‘Master’
  2. Select Tasks -> select Generate Scripts
  3. Next select the tables or object to which you want to generate script
  4. Go to set scripting options ,then click on advance button you find different options with catogories.
  5. In the General category: go to type of data to script
  6. we find 3 options for that
    1. Schema Only
    2. Data Only (really useful)
    3. Schema and Data
    4. Select the appropriate option and click on OK.

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

for a full discussion thread over this refer to the

For more insights refer to

http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table

Hope it helps!!

Advertisements

About saxenapraveen

I work with Microsoft and have 12 years of experience in developing,designing and leading reliable and scalable solutions for cloud and device ready businesses.

Posted on February 15, 2012, in .NET, Sql Server and tagged , . Bookmark the permalink. Leave a comment.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: