Script Data in SQL Server 2008

Use the Generate Script function in SSMS to automate the population of data into some SQL statements.

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.

Script Wizard
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.

Script Wizard
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.

Featured

  • Azure Cost Management Now Commercially Available for Some Tenancies

    Microsoft on Monday announced that its Azure Cost Management feature had reached the "general availability" release stage for both Azure "pay-as-you-go" customers and Azure Government tenancies.

  • Microsoft Bringing Files Restore Capability to SharePoint Online and Teams

    Microsoft on Monday announced that it's delivering its Files Restore feature for SharePoint Online and Microsoft Teams to Office 365 tenancies as early as this month.

  • Microsoft Nabs IoT Platform Provider Express Logic

    As part of its plan to invest $5 billion in IoT technologies, Microsoft this week acquired Express Logic, which provides real-time operating systems for industrial embedded and IoT devices.

  • Dealing with Broken Dependencies in SCVMM

    Brien shows you how to resolve some broken, template-related dependencies in Microsoft's System Center Virtual Machine Manager.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.