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]
Fig 1.0
Click "Next" on the introduction screen. Refer Fig 1.1
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
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
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".
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.
Fig 1.5
SQL Server prepares the schema and data. [Refer Fig 1.6]
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]
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.