How to export table schema and data with Insert statements in SQL Server?

In this example, have created Persons Table with the data. Will see how to export the table schema and data with insert statements.

Follow the below steps.

Step 1: Open SQL Server Management Studio. Connect to the required Database.

Step 2: Right click on the required Database (where the actual table is available) from the Object Explorer, select Tasks and click on Generate Scripts…

SQL Server - Generate Scripts

Step 3: Generate and Publish Scripts windows is displayed as shown below. Click on Next.

SQL Server - Generate and Publish Scripts - Introduction

Step 4: Select the “Select specific database objects” and select dbo.Persons table. Click on Next.

SQL Server - Generate and Publish Scripts - Choose Objects

Step 5: Choose the File name and Path. Click on Advanced.

SQL Server - Generate and Publish Scripts - Set Scripting Options

Step 6: Under Options, Select Types of data to script as Schema and data. Click on OK.

Note:

  1. Data only, data alone exported.
  2. Schema and data, Table schema and data also exported.
  3. Schema only, Table schema alone exported. 

SQL Server - Generate and Publish Scripts - Set Scripting Options - Advanced Scripting Options

Step 7: At this step, can see the summary of our selection. Click on Next.

SQL Server - Generate and Publish Scripts - Summary

Step 8: Status of our Data and Schema export can be seen at this step. Click on Finish.

SQL Server - Generate and Publish Scripts - Save or Publish Scripts

Step 9: Open the file as provided the path in the Step 5 to see the file created by SQL Server.

Below is the output. We can see the Table Schema and Insert Statements.

Using this scripts, we can easily execute the commands to create the table and insert the data in any SQL environments.

SQL Server - Persons Data Folder and File

Hope you liked the post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.