Scripting data out of a table in SQL Server 2008

December 16, 2009

Database administrators often script the schema of a table and generate stored procedures from an existing table and stored procedure respectively. In SQL Server 2008, Microsoft added a new option in scripting: scripting data from a table.

This article illustrates how to use the "Generate Script option" to script out the table data and store it as a script file or in the clipboard.

Note: This article is written using SQL Server 2008 R2 Aug CTP in Windows 7. Though the screens may look different, the functionality illustrated is same as in SQL Server 2008.

Now let's create the database "MyDatabase" using the below shown script.

USE [master]
GO

/****** Object:  Database [MyDatabase]    
	Script Date: 12/11/2009 09:02:34 ******/

IF  EXISTS (SELECT name FROM sys.databases 
	WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
GO

USE [master]
GO

/****** Object:  Database [MyDatabase]    
	Script Date: 12/11/2009 09:01:26 ******/
CREATE DATABASE [MyDatabase] ON  PRIMARY 
( NAME = N'MyDatabase', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\
	  MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf' , 
	SIZE = 1280KB , MAXSIZE = UNLIMITED, 
	  FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyDatabase_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\
	  MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabase_log.LDF' , 
	SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 100
GO

Now let us create the table "CustomerDetails" using the below transact sql commands.

USE [MyDatabase]
GO

/****** Object:  Table [dbo].[CustomerDetails]    
	Script Date: 12/11/2009 09:05:50 ******/

IF  EXISTS (SELECT * FROM sys.objects 
	WHERE object_id = OBJECT_ID(N'[dbo].[CustomerDetails]') 
	AND type in (N'U'))
DROP TABLE [dbo].[CustomerDetails]
GO

USE [MyDatabase]
GO
/****** Object:  Table [dbo].[CustomerDetails]    
	Script Date: 12/11/2009 09:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CustomerDetails](
	[id] [int] NULL,
	[FirstName] [varchar](100) NULL,
	[LastName] [varchar](100) NULL,
	[DoorNumber] [varchar](50) NULL,
	[Street] [varchar](500) NULL,
	[City] [varchar](200) NULL,
	[Zip] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let us insert data to the table "CustomerDetails" using the below transact sql commands. Execute the below given script.

insert into CustomerDetails 
select 1,'Josie','su',100, 'Main st','San francisco',94109
insert into CustomerDetails 
select 2,'Sam','Rooban',10, 'HighBridge road','Somerset',08875
insert into CustomerDetails 
select 3,'Kristina','Sheik',10, 'Essex St','Hackensack',08620
insert into CustomerDetails 
select 4,'Vincent','Hsu',50, 'Main st','Fortlee',07024
insert into CustomerDetails 
select 5,'Shane','Murphy',500, 'West Main st','San francisco',94124

Now, generate script for this table along with data using "Generate Scripts…" option.

In SQL Server management studio, right click on the database "MyDatabase" in Object Explorer, click on the menu item "Tasks" and click on the menu item "Generate Scripts". [Refer Fig 1.0]

Generate Scripts
Fig 1.0

Click "Next" on the introduction screen. Refer Fig 1.1

Click
Fig 1.1

On the Next screen, select the option "Select Specific database objects" and select the "dbo Customer Details" table. Click "Next" as shown in figure 1.2

Select Specific database objects
Fig 1.2

On the next screen you have the option to select the output. You could select "Save to File", "Save to ClipBoard" or "Save to a new query window". For this illustration let's select "Save to ClipBoard" as shown in figure 1.3

select the output
Fig 1.3

Now in the same screen, click on the "Advanced" button as shown in Fig 1.4. Here you can choose various options. One of the options that we are interested in is scripting the data.

Select the value "Schema and data" under the option "Types of data to script".

Select the value Schema and data under the option Types of data to script
Fig 1.4

Click OK on the above screen and click "Next". Verify the options in the next window as shown in Fig 1.5 and click Next.

Verify the options
Fig 1.5

SQL Server prepares the schema and data. [Refer Fig 1.6]

SQL Server prepares the schema and data
Fig 1.6

Press Ctrl + "V" [Windows paste command] in a notepad or on a query Window. You will see that SQL Server has generated both Schema and data for the table "Customer Details" in the database "MyDatabase". [Refer Fig 1.7]

SQL Server has generated both Schema and data for the table
Fig 1.7

From the Script shown below, you see that SQL Server scripted the table data and listed all of the columns as well.

USE [MyDatabase]
GO
/****** Object:  Table [dbo].[CustomerDetails]    Script Date: 12/11/2009 09:28:19 ******/
DROP TABLE [dbo].[CustomerDetails]
GO
/****** Object:  Table [dbo].[CustomerDetails]    Script Date: 12/11/2009 09:28:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerDetails](
	[id] [int] NULL,
	[FirstName] [varchar](100) NULL,
	[LastName] [varchar](100) NULL,
	[DoorNumber] [varchar](50) NULL,
	[Street] [varchar](500) NULL,
	[City] [varchar](200) NULL,
	[Zip] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[CustomerDetails] ([id], [FirstName], [LastName], [DoorNumber], 
[Street], [City], [Zip]) VALUES (1, N'Josie', N'su', N'100', 
N'Main st', N'San francisco', 94109)
INSERT [dbo].[CustomerDetails] ([id], [FirstName], [LastName], [DoorNumber], 
[Street], [City], [Zip]) VALUES (2, N'Sam', N'Rooban', N'10',
 N'HighBridge road', N'Somerset', 8875)
INSERT [dbo].[CustomerDetails] ([id], [FirstName], [LastName], [DoorNumber], 
[Street], [City], [Zip]) VALUES (3, N'Kristina', N'Sheik', N'10',
 N'Essex St', N'Hackensack', 8620)
INSERT [dbo].[CustomerDetails] ([id], [FirstName], [LastName], [DoorNumber], 
[Street], [City], [Zip]) VALUES (4, N'Vincent', N'Hsu', N'50',
 N'Main st', N'Fortlee', 7024)
INSERT [dbo].[CustomerDetails] ([id], [FirstName], [LastName], [DoorNumber], 
[Street], [City], [Zip]) VALUES (5, N'Shane', N'Murphy', N'500', 
N'West Main st', N'San francisco', 94124)

Conclusion

This article illustrated how to use the "Generate Script option" to script out the table data and store it as a script file or in the clipboard.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers