Auto Generate INSERT Statements for a Table in SQL Server

During data migration or preparing meta data, you may come across generating insert scripts for inserting data to a table on production environment or populating a test environment database multiple times. For that you need to generate insert statements for huge amount records.

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. This illustration is done with SQL Server 2016 and SSMS v17.4.

Steps To Auto Generate INSERT Statements

1. In SSMS Object Explorer, right-click the database.
2. From the right-click menu, go to Tasks >> Generate Scripts...
   
3. In the Generate and Publish Scripts pop-up window, press Next to choose objects screen.
4. 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.
   
5. In the Set Scripting Options screen, press Advanced button.
6. 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.
   
   
7. 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.
   
8. In the summary screen, verify the options you have selected and press Next.
9. 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.
   

Reference

• About SSMS generate script options in Microsoft Docs.