Learn how to use the "Generate Script option" in SQL Server 2008 to script data from a table.

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.

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]

Click "Next" on the introduction screen. Refer 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

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

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".

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.

SQL Server prepares the schema and data. [Refer 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]

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