Generate insert Statements from a Specific Table Data using SQL Server

When you are deploying your application, sometimes you may want to generate SQL script with the insert statement of the specific database table. In this article, I will show you two easiest ways to do this with SQL Server and SQL Server tools.

1. Using Database Publishing Wizard (“SqlPubWiz.exe”)

If you install Visual Studio 2008 / Visual Studio 2008 Express full version, it will automatically install the Microsoft SQL Server Data Publishing Wizard at the following location:

• SQL Server Installation Location]\90\Tools\Publishing\[version]\ SqlPubWiz.exe

If it is not there, you can download it from the following URL

• http://go.microsoft.com/fwlink/?LinkId=119368

When you run the “SqlPubWiz.exe”, it will open the database publishing wizard.

Click next and enter the database server connection information accordingly.

Click next, and it will ask you to select the database, which you want to generate the data from:

Next screen will ask to select the Object type. You can use this database publishing wizard to generate data and schema both. In here, I consider only for the data generation part. Now choose “Tables” as an option and click next. (Make sure to uncheck “Script all objects in the selected database”).

Then, next ask to select the table which you want to generate the data from. Select whatever the table you want and click next.

Next step asks to specify the output location. You can enter the file name and the location there.

By clicking Next, it shows to select the publishing options. There are three publishing types, Schema only, Data only, or Schema and data. Here you have to select “Data only”.

Click next and it will generate the insert data script for you.

2. Using SQL Server Generate Script Wizard

When you select “Generate Script” menu item, it will open the Generate SQL Server Scripts Wizard dialog.

By clicking next, it asks to select the database which you want to generate the script.

By clicking Next, it shows to select the script options dialog box. In here, you change the “Script Data” as “True” and click the next button.

Next screen will ask to select the Object type. Now choose “Tables” as an option and click next.

Next screen asks you to select the tables which you want to generate the data from. Select whatever tables and click next.

Next step asks to specify the output options. If you select “Script to file” as an option, then you can enter the file name and the location there.

Next two steps are very straightforward. It shows the script generation summary, and by clicking next, it will generate the script with insert statements for the selected tables.