Create Insert Script From MS Sql Table

Even though the “Generate Script” feature is available introduced long back ago, some of the developers don't aware of this option to create insert script.

For example, the below table is in my database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable1](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Age] [int] NULL,
	[Address] [nvarchar](255) NULL,
	[City] [nvarchar](50) NULL,
 CONSTRAINT [PK_MyTable1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO

I have some data in the table. I want to create an insert script for this table.

Steps:

Right click on the database which contains the table.

Go to “Task” -> “Generate Scripts…” option

Now you will get “Generate & Publish Scripts” Dialog

In the “Introduction” section, click “Next” to skip basic instruction about these options

In “Choose Objects” section, click the radio button on the “Select specific database objects” option. Now, checkbox prior to each database objects is enabled.

Now you can choose the table which you want to generate the insert script.

Then click “Next” to move to “Set Scripting Options” section.

You can choose your output format in this section.

The most important step here to create insert script is, select “Advanced” section.

In the “Advanced Scripting Options”, change “Types of data to script” to “Data only”

There are three option is available

• Data only
• Schema and data
• Schema only

Go through the remaining option, change if you want any. Click “OK” to close the dialog.

Click “Next” to see the Summary option

Click “Next” to see the process of the scripting. After the successful script generation, click “Finish” to see the output.

USE [DBNAMEHERE]
GO
INSERT [dbo].[MyTable1] ([Id], [Name], [Age], [Address], [City]) VALUES (1, N'Will Smith', 50, N'Philadelphia, Pennsylvania, USA', N'Philadelphia')
GO
INSERT [dbo].[MyTable1] ([Id], [Name], [Age], [Address], [City]) VALUES (2, N'Leonardo DiCaprio', 44, N'Hollywood, Los Angeles, California, USA', N'Los Angeles')
GO
INSERT [dbo].[MyTable1] ([Id], [Name], [Age], [Address], [City]) VALUES (3, N'Ben Kingsley', 75, N'Scarborough, Yorkshire, England, UK', N'Scarborough')
GO

DDL Statements

To generate DDL statements, choose Schema only option in the Types of data to script”. And also you can specify SQL Server Version for the script under “Script for Server Version” to generate backward compatibility scripts