SQL SERVER – Auto Generate INSERT Statements For A Table In SQL Server

To generate insert scripts for inserting data to a table on production environment or populating a test environment database follow this procedure

In SQL Server Management Studio (SSMS), there is an option to easily auto generate insert statements on the fly. Below are the steps to generate insert statements using SSMS.

In SSMS Object Explorer, right-click the database.

From the right-click menu, go to Tasks >> Generate Scripts.

In the Generate and Publish Scripts pop-up window, press Next to choose objects screen.

Now, the choose objects screen, choose Select specific database objects and choose the tables you want to script. You can even select all the tables and other objects. After choosing the tables, press Next.

In the Set Scripting Options screen, press Advanced button.

On the pop-up Advanced Scripting Options screen, under General section, in the field Types of data to script, select the either Data Only or Schema and Data or Schema Only based on your requirement. Press OK.

Back in the Set Scripting Options screen, you have the option to save the script to a file or to the clipboard or to a Query Window. For this illustration, I’ve selected the option Save to new query window. Finally press Next.

In the summary screen, verify the options you have selected and press Next.

Finally you will see the status screen, you will see the status of the operations.Once actions are successful, you will notice the Insert script along with the schema is generated in a new query window. Press Finish to close the generate scripts window.

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 )

Connecting to %s