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...
    Auto Generate INSERT Statements in SQL Server 01
  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.
    Auto Generate INSERT Statements in SQL Server 02
  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.
    Auto Generate INSERT Statements in SQL Server 03
  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.
    Auto Generate INSERT Statements in SQL Server 04
  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.
    Auto Generate INSERT Statements in SQL Server 05

Reference


7 thoughts on “Auto Generate INSERT Statements for a Table in SQL Server”

  1. This is nice, but…this is whole tables only. Correct? How does one constrain the records to a selection? I’m really discouraged that DataGrip can simply copy/paste insert statements from the output window and SSMS still does not have that functionality (as far as I know.)

    Reply
  2. Hi there, interesting article! Is there a way of achieving the same with duplicate check before inserting? I mean, is there an option on SSMS that adds a WHERE NOT EXISTS on the insert, to prevent duplicate records?

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.