SQL Nugget - script content of a table as INSERT statements

I have wrestled a couple of times what was easy when I used TOAD but became painful with SQL Server.

For example you have a control table that is manually edited most of the times in DEV. When you want to move it to TEST you can transfer the whole table but in terms of source control it's better to have it in the form of INSERT statements. This way it's much easier to extend and put additional rows. And I've experienced many times that I used my dev PC and created a good table there but couldn't transfer it to the DEV server with the Export/Import wizard. It just gave me error messages that I didn't know why happened or it would have been too much of a hassle to correct it.

In cases like this I generally maintain a metadata repo in a spreadsheet and create the inserts with string functions inside the spreadsheet. Clumsy, I agree.

Today I figured it out how to do that much easier (I think it's not a novelty to many of my fellow developers though :))

  • Right click onto a database in SQL Management Studio
  • Navigate to Tasks\Generate Scripts
  • Choose database (next)
  • Choose object (e.g. tables)
  • Now comes the tricky part! Scroll down until you can see Script Data (this is set to FALSE by default but now set it to TRUE!)
  • Tick the objects you want to script.

After some more next and finish you can have a CREATE TABLE script along with INSERT statements for all the rows from that table!

USE [Database Name]

GO

CREATE TABLE [dbo].[TableName](

[Column1] [varchar](150) NULL,

[Column2] [int] NULL,

...)

GOSET ANSI_PADDING ON

GO

INSERT [dbo].[TableName] ([Column1], [Column2], ...) VALUES ('One', 1, ...)

INSERT [dbo].[TableName] ([Column1], [Column2], ...) VALUES ('Two', 2, ...)

...

SET ANSI_PADDING OFF

Published Wednesday, October 20, 2010 5:53 AM by MartinIsti
Filed under: , , ,

Comments

No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems