Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,050,649 hits
  • Select GETDATE()

    December 2013
    M T W T F S S

Did you know? You can generate Insert Statement with data using Management Studio!!!

Posted by blakhani on December 13, 2013

I must confess that at least I didn’t know about that option till I saw an internal email thread. And my first reaction was – Damn! all these years and never spotted that! Yes, we can get all of the INSERT statements for the data straight out of SSMS. Another hidden feature of SSMS.

Earlier, I used to search on internet and get some stored procedure to get the insert statements generated for me. Here were few search strings which used to give me proper results “SQL Generate Insert Script”, “SQL Server Generate Insert Script”, "SQL Server Script Insert Statements", "Create Insert Script SQL Server", "Generate Insert Script from Table"

Now I can avoid all those searches and do that right from the Management Studio. For demo purpose I would create a small database and show that option. Please note that I am using SQL Server 2012 Management Studio (SSMS 2012)

Use myAppDB
Create table HelloSQL (iID int, vFname varchar(100), vLName varchar(100))
insert into HelloSQL values 
(1, 'Balmukund', 'Lakhani'),
(2, 'Vinod', 'Kumar M'),
(3, 'Pinal', 'Dave'),
(4, 'Sourabh', 'Agarwal'),
(5, 'Amit', 'Banerjee')
Select * from HelloSQL


To achieve our insert statements with script use Object Explorer and connect to an instance of the SQL Server. Expand Databases folder, Choose the database for which we want the script, right click, Tasks > Generate Scripts.

Once Wizard is launched, its self-explanatory to navigate through screen.


After welcome screen, we need to choose the object (table HelloSQL in our case) for which we want script to be generated.


In next screen, we need to choose the destination of script. I have selected “Save to new query window”


In the same screen, we have a magical button called “Advanced”. That’s where we have many options about scripting.


I have selected “Schema and Data” under “Type of data to script” and hit OK. Hit Next on previous screen


hit Next and Finish.


… and we have what we needed. Schema creation as well as Insert Statement for the data in table.


There are many other options in advanced tab. Play with them and get the desired script. I have learned this and it was new to me. Hope this is useful to you also. SQL Server generate script with data is a powerful SQL Server tool in order to create SQL script to move data from one database to another database. SQL programmers can export data as sql script by using one of the existing SQL Server tools, SQL Server Generate Script Wizard.



Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOnPaperback, Kindle


9 Responses to “Did you know? You can generate Insert Statement with data using Management Studio!!!”

  1. Aman Ankit said

    Wonderful stuff.. Thanks again… I read all your blogs and attend all your sessions @ Microsoft.

  2. deepti said

    thanks it was a new learning for me..

  3. Amir said

    I really like the way you explain the tricks and solutions. Really helpful. Thank you for sharing knowledge and helping others 🙂

  4. Sorry to burst the bubble, but when using 2102 server express, no data is ever created – even with the option chosen.

  5. Abhishek Bhalerao said

    Hi Balmukund.. Thanks for the tip. it was really helpful. Saved lot of time & effort.

  6. sam dias said

    Nicely done

  7. JK said

    Wonderful script. Great Job

  8. i think microsoft has buried this option for fear it will make it easy to migrate to a better/cheaper database like mysql

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

%d bloggers like this: