Generating Create table and Insert script with data using SQL Server 2008 Management Studio

In today's example we will see a way to generate create table and insert script using SQL Server 2008 Management studio.

This feature is available from SQL Server 2008 version and is really helpful, if you have data already available in one or multiple tables and you want to generate insert script for that data.

Following are the steps which needs to be followed for generating the insert script:-

Step 1:- Right click on the Database which has the table using which you want to generate the insert script. 

        <Right Click on Database> -> Task -> Generate Scripts

Step 2:- Click on "Choose Object" option. 

By default "Script entire database and all database objects" will be selected.

As i have to generate insert scripts for only for one table. I will click on 2nd option.

Step 3:- Click on "Select specific database objects". 


Step 4:- Then select the table option and all the tables for the 
         selected Database will be shown.

I have to generate the create and insert script for "Roles" table so i have selected the same from the tables list. Click on next button.

Step 6:- In this screen different options are available.

1)Select a location where you want to save the file.
2)Publish to a web service
3)Whether single file per object or Single file for all the 
  objects
4)Saving to Clipboard
5)Saving to new query window.
Click on the "Advanced" button.

Step 7:- This is the most important step. 

There are three options available:
1) Data only - If you want to generate the insert script for data 
               only.
2)Schema and Data - If you want to generate the create table 
                    script with the insert script of the 
                    available data.
3)Schema only - If you want to generate the create table script.

Step 8:- I have selected "Schema and data" option. Click Ok button.

And then click on Next button for rest of the options.

Finally screen will be shown with successful result. 

And the following script is generated for my table and data available in that table:


USE [ReportServer$SQLSERVER2008]
GO
/****** Object:  Table [dbo].[Roles]    Script Date: 09/07/2012 22:33:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[RoleID] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](260) NOT NULL,
[Description] [nvarchar](512) NULL,
[TaskMask] [nvarchar](32) NOT NULL,
[RoleFlags] [tinyint] NOT NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY NONCLUSTERED 
(
[RoleID] 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
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'af71b93f-9845-4c7e-82fd-9c3590e49f88', N'Browser', N'May view folders, reports and subscribe to reports.', N'0010101001000100', 0)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'f45d0872-5c54-46db-a54a-8148a5eeb270', N'Content Manager', N'May manage content in the Report Server.  This includes folders, reports and resources.', N'1111111111111111', 0)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'c2073b90-d63f-4128-9739-82ffb11393b4', N'Model Item Browser', N'Allows users to view model items in a particular model.', N'1', 2)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'c70a4cd7-d31c-45f7-8d37-7e0b54b49031', N'My Reports', N'May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.', N'0111111111011000', 0)

Amazing work right. Truly speaking it can save a lot of time for developers as normally we generate script manually most of the time which takes lot of time.

Happy Coding......