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)

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

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.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle