THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Script Data in SQL Server 2008

Here is a feature that I have been waiting for since I started working with SQL Server 6.5; the ability to script the data from a table. When you deploy a SQL Server database, you often need to include data in several tables. This can be for lookup values, configuration tables, or just seed data. In the past, you had to manually write a script to insert the data. You could also write a creative SELECT statement that would include hard coded text to append the ‘INSERT INTO……’ logic into a string and then concatenate the data values. Both of these methods are a bit of a pain. With SQL Server 2008, the Generate Script functionality in SSMS allows us to script the data out of a table into a batch of INSERT statements. Let’s walk through this in detail.

You need to Right-Click a database and select Tasks | Generate Scripts. This opens the script wizard, as shown, that you use have used for all your SQL Server schema scripting needs. Click Next to get rolling.

clip_image002

Select the database that you want to script the data from and click Next. This brings you to the Script Option page and this is where the magic happens. If you scroll down to Table/View Options, as shown, you will see all the script options. Among these options is Script Data. Set that to True and specify any of the other options you want for your script. When you are ready, click Next.

clip_image004

From here, the wizard is the same as if you’re not scripting data. You select the object types (tables, views, stored procedures) you want to script followed by screens that let you choose all the specific objects. When you complete the wizard, your script will now include INSERT statements based on the data in the table. Here is an example for the table I scripted.

INSERT [dbo].[Employee] ([employeeid], [firstname], [lastname], [birthdate], [gender], [managerid], [departmentid], [active_date], [inactive_date])
VALUES (5, N'Kevin', N'Brown', CAST(0xD0030B00 AS Date), N'M', 20, 2, CAST(0x52320B00 AS Date), NULL)

INSERT [dbo].[Employee] ([employeeid], [firstname], [lastname], [birthdate], [gender], [managerid], [departmentid], [active_date], [inactive_date])
VALUES (8, N'Rob', N'Walters', CAST(0x2EF20A00 AS Date), N'M', 5, 2, CAST(0x52320B00 AS Date), NULL)

Hopefully this saves you some work the next time you need to include insert statements in a deployment script.

Published Monday, March 08, 2010 10:00 AM by ejohnson2010

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

A nice addition, but a warning though - if you're wanting to move tables from place to another (in particular substantial tables), this is an extremely inefficient technique. Instead for large amounts of data you should be using the SSIS Import/Export options.

What this is awesome for (and I think this is where you're using it Eric) is if you have lookup tables that you need to provide with "seed" values - it makes building an upgrade or build script extremely easy.

March 8, 2010 11:26 AM
 

Lars said:

The ability to generate insert scripts is trivial. We have always had a stored proc from Vyas, we have Data Compare from Red Gate, we already have a solution. Why bother developing another one when we already have very good solutions?

At the same time all those broken windows are not fixed: the UDFs are still very slow, the error handling is disfunctional, etc. We cannot have a third party solution for these serious problems.

Also there are much less OLAP functions than in Oracle in 2003, and we cannot develop our own OLAP functions.

Why don;t they concentrate on fixing what is broken before spending resources on easy problems we already have solutions for?

March 9, 2010 11:14 AM
 

Jason Haley said:

I can't seem to find the Script Data option in SQL 2008 R2 ...

March 9, 2010 3:14 PM
 

Ranga Narasimhan said:

Wow!!! Sometime MS hides their new/good features.

May be they should have some color schemes for newly added features so it will catch our attention!

April 2, 2010 2:27 PM
 

E. said:

@Jason Haley: in SQL 2008 R2 they changed the property name. You can find it under "Types of data to script".

April 29, 2010 1:44 PM
 

Thomas said:

Need this for previous versions? Check out SQL Scripter (www.sqlscripter.com). Not for free, but much more scripting  features...

June 1, 2010 1:53 PM
 

John Jakob said:

>>  In SQL 2008 R2 they changed the property name. You can find it under "Types of data to script".

Yeah, there it is!  Thanks, man.

January 18, 2011 12:52 PM
 

Dave said:

How can we Write actual date value instead of [CAST(0x52320B00 AS Date)] part?? I've to use these scripts in DB2 database and that is where it is a problem for me..

April 6, 2011 5:10 AM
 

Ben said:

Thanks guys, using the import/export data was a pain. This is much better because you can script all your DB changes in one big file.

April 21, 2011 9:28 AM
 

Farid said:

Thanks, i had a feeling they should have such a function....

May 22, 2011 9:21 PM
 

NCI said:

Nice Addition, thanks it is working

August 12, 2011 11:29 AM
 

Jithu said:

How can we Write actual date value instead of [CAST(0x52320B00 AS Date)] part?? I've to use these scripts in DB2 database and that is where it is a problem for me..

August 30, 2011 2:10 AM
 

dyadyalyonya said:

Thanks!

This feature is very useful for me.

September 19, 2011 10:57 AM
 

Kerry said:

Thankss a lot Eric ...this makes my work easy thankxx a lot buddy....:)

October 23, 2011 3:57 AM
 

Colleen said:

This is a great feature but, I need to use this as a job that runs nightly. How do I get the script that generates the script?

December 1, 2011 7:12 PM
 

Partho Ghosh said:

I saw <a

href="http://blogunlimited.com/MohammedRashid/archive/2012/03/10/generate-insert-statements-for-sql.aspx">this

post</a> very helpful for generating SQL Insert statements from an existing

database table in SQL Server 2005 with options to filter and sort the output data. It works for

IDENTITY columns also. Hope it may be useful to others as well who are looking for INSERT statement generator script for SQL Server 2005.

March 18, 2012 11:02 PM
 

Partho Ghosh said:

My previous comment was not appearing properly. Here is the link that I wanted to give for SQL Server 2005 INSERT statement generator:

http://blogunlimited.com/MohammedRashid/archive/2012/03/10/generate-insert-statements-for-sql.aspx

March 18, 2012 11:05 PM
 

Jason Gallespie said:

I found a very simple and excellent INSERT script tool which is a SSMS Addin that installs in the right click menu of the management studio! Only $5 a license and they have plans to incorporate more features soon. Great value in my opinion.

Link is <a href="http://www.mssql-vehicle-data.com/SSMS">http://www.mssql-vehicle-data.com/SSMS</a>

April 10, 2012 5:24 AM
 

Jason Gallespie said:

I found a very simple and excellent INSERT script tool which is a SSMS Addin that installs in the right click menu of the management studio! Only $5 a license and they have plans to incorporate more features soon. Great value in my opinion.

Link is http://www.mssql-vehicle-data.com/SSMS

Horrible link on this blog! sorry for the repost!

April 10, 2012 5:26 AM
 

Nauman said:

Is it possible to add "Go" keyword right after each INSERT statement? By using such keyword sql query consumes less system memory.

September 18, 2012 1:25 AM
 

brzooz said:

Thanks for this informations.

January 2, 2013 6:46 AM
 

Karen Hillebrand said:

Thank You!

January 11, 2013 1:20 PM
 

Mike Letson said:

I've been looking for that for a while.  Thank You!  Makes my life easier.

March 5, 2013 9:17 AM
 

gf said:

Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well

May 28, 2013 3:28 AM
 

ashger abbas raj Company said:

Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has

May 28, 2013 3:29 AM
 

DDtankPS said:

Thank Man.. But SQL Server 2008 little difference from your photos.. ^_^

June 24, 2013 7:20 AM
 

Rahul said:

Can I generate data script in sql server 2008 . I don't have R2

March 7, 2014 8:53 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement