SQL Server: Generate Insert Statement Script

When running reports, for example, the same insert statements need to be created over and over again. SQL Server includes a great feature to generate these insert statements automatically.

For this example, I be working with the emp table with 9 rows in my database. Now, many months later, I need to generate an insert statement for this table.

1. Open SQL Server Management Studio and go to Object Explorer.

2. Right click on your database. Go to Tasks then Generate Scripts.

 

3. You will find a window titled “Generate and Publish Scripts” and an introduction is displayed. Just click on Next to proceed.

4. In the Choose Objects window select the radio button Select specific database objects and expand the Tables tree structure. Select the table name for which you are going
to generate the insert statements. Then click on Next. In my example, I am selecting the emp table below.

 

5.  Now in Set Scripting Options window, you need specify how you are going to save your script. Let save to a file by selecting Save script to a specific location and then Save to file with Single file option. You will need to make sure you note the path. Then select the Advanced button.

 

6. Now in “Advanced Scripting Options” window in General go to Types of data to script and select Data only. Click on OK and then click on Next.

 

7. Now you will find the Summary window which shows the information about the selected database, tables, and target file path. Click on Next.

8. Now in Save or Publish Scripts window will show the results of success.

9. In the target file you will find the insert statements for the table.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.