<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Export Table Data in SQL Server Management Studio

Published on
3,582 Points
582 Views
Last Modified:
Dirk Strauss
Software Developer, Microsoft MVP, Coffee Drinker, Author and Professional Grouch. dirkstrauss@protonmail.com
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.

Did you know that you can generate INSERT scripts form your data tables in SQL Server Management Studio? Consider the OrderStatus table in the image below:



If we wanted to create INSERT scripts from the data in the table, right-click on the database name and select Tasks > Generate Scripts... from the context menu.



The Generate and Publish Scripts window will open. If you want to generate scripts for several objects, you can do so. Here, I just want the INSERT scripts for a single table. So I click on Select specific database objects and check the OrderStatus table. Then click on the Next> button.


The next screen allows you to output the data to a file, clipboard or new query window. Here I will just output it to a new query window. Before clicking Next> , you need to click on the Advanced button.



Doing this will open up the Advanced Scripting Options. Go ahead and change the selection for Types of data to script to Data only and click on OK. Then click Next> until you complete the wizard and finally click Finish.


USE [TestDatabase]
GO
SET IDENTITY_INSERT [dbo].[OrderStatus] ON 


GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (1, N'Order Placed', 0)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (2, N'Check funds', 1)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (3, N'Waiting for Order Approval', 2)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (4, N'Waiting to Print Label', 3)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (5, N'Waiting to Print Packing Slip', 4)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (6, N'Waiting to Charge Credit Card', 5)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (7, N'Waiting to Send email', 6)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (8, N'Waiting Completed', 7)
GO
INSERT [dbo].[OrderStatus] ([recId], [OrderStatusText], [OrderStatusID]) VALUES (9, N'Order Completed', 8)
GO
SET IDENTITY_INSERT [dbo].[OrderStatus] OFF
GO

The INSERT script for the data of the table is generated for you.

0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month