A while back, I saw the following tweet mentioning that you can’t script out data from SQL Server tables.
It turns out, though, that you can do this. And as long as you are using SQL Server Management Studio 2008 or higher, you have access to the tools necessary. To demonstrate, I’ll walk through the steps to create scripts for all of the data in the table HumanResource.Department in the AdventureWorks2012 database.
First, select the Tasks –> Generate Scripts… option from the right-click menu one one of the databases, in this case I selected AdventureWorks2012, doing this launches the Generate and Publish Scripts wizard.
On Introduction page of the Generate and Publish Scripts wizard check the box for Do not show this page again, click Next. This step could be skipped, but eventually you’ll probably do this.
On the Choose Object page of the Generate and Publish Scripts wizard, select from the Table tree the tables that you need to script out data, click Next. In this example, we are only going to return the data from the table HumanResources.Department.
On the Set Scripting Options page of the Generate and Publish Scripts wizard, select Save to new query window, click Advanced. There are other options for exporting the data, such as the clipboard, file, or a web service.
On the Advanced Scripting Options page of the Generate and Publish Scripts wizard, change the option for Types of data to script from Schema only to Data only. This one option changes the output from all schema to all data. There is another option that can include data and schema in the output. Click OK and then click Next.
On the Summary page of the Generate and Publish Scripts wizard, there are no options to configure, click Next. This page provides a summary of the work being requested, clicking next starts the scripting process.
On the Save or Publish Scripts page of the Generate and Publish Scripts wizard, there are, again, no options to configure. This page monitors the scripting process and reports any errors encountered. When complete, click Finish to close the wizard.
In the query window that was opened, there will be a script with all of the required INSERT statements (as shown in the results below). It also includes the IDENTITY_INSERT as needed to ensure that the data is inserted exactly as it appears in the source table.
GO
SET IDENTITY_INSERT [HumanResources].[Department] ONGO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (1, N’Engineering’, N’Research and Development’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (2, N’Tool Design’, N’Research and Development’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (3, N’Sales’, N’Sales and Marketing’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (4, N’Marketing’, N’Sales and Marketing’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (5, N’Purchasing’, N’Inventory Management’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (6, N’Research and Development’, N’Research and Development’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (7, N’Production’, N’Manufacturing’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (8, N’Production Control’, N’Manufacturing’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (9, N’Human Resources’, N’Executive General and Administration’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (10, N’Finance’, N’Executive General and Administration’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (11, N’Information Services’, N’Executive General and Administration’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (12, N’Document Control’, N’Quality Assurance’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (13, N’Quality Assurance’, N’Quality Assurance’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (14, N’Facilities and Maintenance’, N’Executive General and Administration’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (15, N’Shipping and Receiving’, N’Inventory Management’, CAST(0x0000921E00000000 AS DateTime))
GO
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (16, N’Executive’, N’Executive General and Administration’, CAST(0x0000921E00000000 AS DateTime))
GO
SET IDENTITY_INSERT [HumanResources].[Department] OFF
GO
I’ve used this a couple times for generating deploy scripts and it’s worked very well. A very nice addition to SQL Server Management Studio.
Strate SQL 