Scripting data out of a table in SQL Server 2008

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

Latest Articles