Thursday, July 28, 2016

How To Export A Sql Table As A Series Of INSERT Commands in Sql Server 2012

Occasionally as developers we need to copy the contents of a database table to another remote sql server that we cannot access.  We can export an Excel spreadsheet and email that to our counter parts at the remote machine.  Another option is to create a file of SQL insert commands that will transfer the data. 
In Sql Server Management Studio right click on the database containing your table,
Select "Tasks/Generate Scripts"

 
 Skip the intro screen and then select the table to export
 

Click "Next"
 Then select "Advanced" and set "Types of data to script" to be "Schema and data" (or only "Data" depending on your needs).

 Select "OK" in the panel.  Then enter a "File  name:" and then "Next>".



Then select "Next>"

And then "Finish".
And now in your export file "~/Documents/scripts.sql" are the commands to create and fill your table.



USE [MyPets]
GO
/****** Object:  Table [dbo].[MYPETS]    Script Date: 7/28/2016 1:51:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MYPETS](
    [Age] [int] NOT NULL,
    [Animal] [char](10) NOT NULL,
    [Name] [varchar](30) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MYPETS] ([Age], [Animal], [Name]) VALUES (1, N'Dog       ', N'Rover               ')
INSERT [dbo].[MYPETS] ([Age], [Animal], [Name]) VALUES (82, N'Turtle    ', N'Yurtle              ')
INSERT [dbo].[MYPETS] ([Age], [Animal], [Name]) VALUES (5, N'Llama     ', N'Rama                ')



Enjoy.