How To Generate Script Of Table With Data In SQL Server

Recently, we were working on an SQL Server database migration project. For the critical tables, we generated scripts, including their data, which made our job easy during migration activities. In this article, I will walk you through all the steps to generate the script for the table with data in SQL Server in real-time.

Generate Script Of Table With Data In SQL Server

Follow the below steps.

1. Connect to the SQL server management studio (SSMS).

2. Expand the database folder and select the database that your table belongs to.

3. Right-click on the Database name –> Tasks –> then click on the Generate Scripts option, as shown in the screenshot below.

4. Click on the Next button.

Check out: How To Create A Table With Identity Column In SQL Server

5. Choose the Select specific database objects option, select the particular table, and click the Next button.

6. Select Open in the new query window option and then click on the Advanced option, as shown in the screenshot below.

7. In the Advanced scripting options, select the Types of data to the script as Schema and data, and then click on the OK button and click the Next button on the main window, as shown in the screenshot below.

Check out How To Create A User In SQL Server Database

8. Click on the Next button on the Summary page.

9. Finally, click on the Finish button.

Check out How to Alter Table Column to Allow NULL in SQL Server

Now, as shown in the screenshot below, the new query was generated in the new query window.

USE [Test]
GO
/****** Object:  Table [dbo].[MyProduct]    Script Date: 19-11-2024 13:57:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyProduct](
	[SalesID] [int] IDENTITY(1,1) NOT NULL,
	[ProductSaleDate] [datetime] NOT NULL,
	[CustomerID] [int] NULL,
	[ProductID] [int] NULL,
	[TotalQuantity] [int] NULL,
	[TotalAmount] [decimal](10, 2) NULL
) ON [ps_MyProductSalePerYear]([ProductSaleDate])
GO
SET IDENTITY_INSERT [dbo].[MyProduct] ON 
GO
INSERT [dbo].[MyProduct] ([SalesID], [ProductSaleDate], [CustomerID], [ProductID], [TotalQuantity], [TotalAmount]) VALUES (1, CAST(N'2022-04-15T00:00:00.000' AS DateTime), 1, 501, 3, CAST(101.90 AS Decimal(10, 2)))
GO
INSERT [dbo].[MyProduct] ([SalesID], [ProductSaleDate], [CustomerID], [ProductID], [TotalQuantity], [TotalAmount]) VALUES (2, CAST(N'2023-05-22T00:00:00.000' AS DateTime), 2, 301, 2, CAST(32.45 AS Decimal(10, 2)))
GO
INSERT [dbo].[MyProduct] ([SalesID], [ProductSaleDate], [CustomerID], [ProductID], [TotalQuantity], [TotalAmount]) VALUES (3, CAST(N'2024-06-30T00:00:00.000' AS DateTime), 3, 701, 1, CAST(103.78 AS Decimal(10, 2)))
GO
SET IDENTITY_INSERT [dbo].[MyProduct] OFF
GO
/****** Object:  Index [PK_Sales]    Script Date: 19-11-2024 13:57:14 ******/
ALTER TABLE [dbo].[MyProduct] ADD  CONSTRAINT [PK_Sales] PRIMARY KEY NONCLUSTERED 
(
	[SalesID] ASC,
	[ProductSaleDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [ps_MyProductSalePerYear]([ProductSaleDate])
GO

Conclusion

Generating scripts for tables with data in SQL Server is crucial when working on a SQL server migration project or creating a test environment. The above steps can help you achieve this.

You may also like following the articles below.

• How To Save Query Results As Table In SQL Server
• SQL Server Create Table 2 Primary Key
• How To Create A Table Variable In SQL Server