Generate Insert Statements For a SQL Server Table

Microsoft SQL Server Database Publishing Wizard is a great tool to generate script for both schema and data for a database. However what if you were to write a script that could do that for you.

Here’s a real cool script written by johnnycrash that generates Insert Statements for a SQL Server Table.

For demonstration purposes, I am using the Culture table of the AdventureWorks database.

USE AdventureWorks
GO
DECLARE @Columns VARCHAR(max);
SET @Columns = '[CultureID], [Name], [ModifiedDate]'
DECLARE @Table VARCHAR(max);
SET @Table = 'Production.Culture'

DECLARE @SQL VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '',
''INSERT INTO ' + @Table + '(' + @Columns + ')'')
+ CHAR(13) + CHAR(10)
+ ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Columns, ',', ' + '', '' + '),
'[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''')
+' FROM ' + @Table
+ ' PRINT @S'

EXEC (@SQL)

OUTPUT

Now that’s cool!

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook