Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing Skills

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)

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.

image

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

image

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

image

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

image

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

image

hit Next and Finish.

image

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

image

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 AlwaysOnPaperback, Kindle

About these ads

2 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..

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,084 other followers

%d bloggers like this: