Script Data in SQL Server 2008
Use the Generate Script function in SSMS to automate the population of data into some SQL statements.
- By Eric Johnson
- 01/18/2010
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 (Fig. 1) that you use have used for all your SQL Server schema scripting needs. Click Next to get rolling.
 |
| Figure 1. Open the Script Wizard by right-clicking a database and then clicking on Tasks | Generate Scripts. |
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 (Fig. 2), 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.
 |
| Figure 2. Select the tables using the Table/View Options dialog. |
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.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.