Elon's Blog

Stuff that may be of use to someone…

Auto Generate INSERT statements for a SQL Server 2008 Table using SSMS

Posted by Elon B. on November 18, 2011

I was doing some SSIS development for a client and had to generate and alter some test data for some packages.

I already had a table with values in that I wanted to capture via INSERT statements, so that I could chop and change the values as needed.

There is a method in SQL Management Studio 2008 (SSMS) that allows you to create these statements right out of the box for SQL Server 2008, without using any scripts or add-ins from SSMS.

So, for example,  to create the INSERT statements for the Table: HumanResources.Department in the AdventureWorks database.          (Note: Click on images to enlarge)

Chosen TableFig 1

Right Click on the Database Name, select Tasks|Generate Scripts…

Click the Next button on the “Generate and Publish Scripts” dialog box that appears.

From the “Choose Objects” screen, select the table from drop down menu. We are doing this for a single table, but as you can see you can do this for multiple objects or the entire database.

Click Next.

On the next screen, decide where you wish to output the script to. I chose a “new query window” in my example.

Then Press the Advanced Button.

From the Advanced Scripting Options dialog, select the Drop-down options for the “Types of data to script” option.  You can choose (i) Data Only (ii) Schema and data or (iii) Schema only

Click OK, and then select the Next Button. Click the Next button again from the next screen and then the Finish button after the task has completed

In this example, the CREATE TABLE and INSERT statements appear for my chosen database table within a new query window in SSMS.

I can now extract and edit the INSERT statements, as required, to create new or different test data for my SSIS project.

Advertisements

Please 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: