How to Generate Data Scripts for Your Tables

Generating Data into scripts is a common problem faced by database developers. Many times you need to use third party tools. With SQL Server 2008, there is an inbuilt feature to generate Data scripts. Follow the steps given below.

1. Right-Click the Database and Select Generate Scripts… from the Task context menu.
2. In the Script option dialog select True fro Script Data option. By default this is true. If you have logged into SQL Server 2005 server, still you can enable this option.

 

 

3. After this option next options are normal options that you will get.

Following is the sample script that was generated by using above option.

USE [AdventureWorks]

GO

/****** Object:  Table [HumanResources].[Department]    Script Date: 01/02/2010 21:32:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [HumanResources].[Department](

      [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,

      [Name] [dbo].[Name] NOT NULL,

      [GroupName] [dbo].[Name] NOT NULL,

      [ModifiedDate] [datetime] NOT NULL,

 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

(

      [DepartmentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [HumanResources].[Department] ON

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (1, N'Engineering', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (2, N'Tool Design', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (3, N'Sales', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (4, N'Marketing', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (5, N'Purchasing', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (6, N'Research and Development', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (7, N'Production', N'Manufacturing', CAST(0x00008C6900000000 AS DateTime))

SET IDENTITY_INSERT [HumanResources].[Department] OFF

/****** Object:  Default [DF_Department_ModifiedDate]    Script Date: 01/02/2010 21:32:38 ******/

ALTER TABLE [HumanResources].[Department] ADD  CONSTRAINT [DF_Department_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

GO