SQL Server Portal

Few months back, I published an article namely How to generate Insert Statements from Table Data using SQL Server. In this article, I have developed a tool that can help you to generate insert into statement from table data.

Today, we will discuss one more tool namely “Microsoft SQL Server Database Publishing Wizard” that will help you not only to generate the insert into statement but also you can generate script for all objects in SQL server. You can download this tool from here

Lets proceed step by step to generate it.

Step 1 :

Run this utility and this will be the welcome screen.

Step 2 :

Once you click on next, you need to give the server name and its credentials. You can also provide it as a connection string.

Step 3 :
In this step you need to select the database. There is no multi selection option, so you need to select one database at a time.
Also, if you check “script all objects in the selected database” then in the next step it will not give you the option to select the objects.

Step 4 :
If you did not check “script all objects in the selected database” then this tool will allow you to select multiple object of the SQL server. ‘Select All’ option is also available.

Step 5 :
In the previous step, I selected the ‘Tables’ so in this step, I need to define which table(s) should be scripted.

Step 6 :
In this step , you can define the path where the script should be generated or you can define the server where the script should be deployed.

Step 7 :
In this step, you need to define your requirement. Select one of the options ‘Data only’ or ‘Schema only’ or ‘Schema & data’.

Step 8 :
In this step, you can see the summary of your selection. If it is not correct, you can go back and change the selection.

Step 9 :
In this step, you can view the result of process, whether it is executed successfully or not.

Step 10 :
Now, the script has been generated successfully and you can get the file from your defined location.
For Example:

/****** Object:  Table [HumanResources].[Department]    Script Date: 09/24/2012 10:48:11 ******/
DELETE FROM [HumanResources].[Department]
GO
/****** Object:  Table [HumanResources].[Department]    Script Date: 09/24/2012 10:48:11 ******/
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))

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

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (9, N'Human Resources', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (10, N'Finance', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (11, N'Information Services', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (12, N'Document Control', N'Quality Assurance', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (13, N'Quality Assurance', N'Quality Assurance', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (14, N'Facilities and Maintenance', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (15, N'Shipping and Receiving', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (16, N'Executive', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

SET IDENTITY_INSERT [HumanResources].[Department] OFF