KeyLimeTie Blog

SQL Server 2005 Script to Generate INSERT statements

By Brian Pautsch – 3/24/2009. Posted to Code Snippets.

When it comes time to migrate code and database changes for a project, you often need to create new rows in the various environments (i.e. QA, Staging, Production). Some migrations involve creating rows for list tables, including states, countries, status codes, etc. One option is to write the INSERT statements one at a time, but that is too time consuming and is prone to errors. Instead, you can use the SQL script listed below.

When you run the script against a table, you'll notice 2 things:
1. All data values are converted to HEX so you don't need to worry about escaping quotes.
2. If the table has an identity column, the script will generate the IDENTITY_INSERT commands as well.

Download SQL script

/* EXAMPLES
--Entire table
CreateInserts 'Webpages'
--Entries WHERE WebpageID > 100
CreateInserts 'Webpages', 'WebpageID > 100'
--Entries WHERE WebpageID > 100 ORDER BY Title
CreateInserts 'Webpages', 'WebpageID > 100', 'Title'
*/--If stored procedure already exists, drop it
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[CreateInserts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateInserts]
GO

--Create the stored procedure
CREATE PROC CreateInserts

@tableName nvarchar(100),
@whereClause nvarchar(MAX) = '',
@orderByClause nvarchar(MAX) = ''

AS

--Declare variables
DECLARE @tableHasIdentity bit
DECLARE @sql nvarchar(MAX)
DECLARE @cols nvarchar(MAX)
DECLARE @vals nvarchar(MAX)
SET @cols = ''
SET @vals = ''

--Determine if table has an identity column
SELECT @tableHasIdentity = 
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tableName

--Do we need 'SET IDENTITY_INSERT tableName ON' statement?
IF @tableHasIdentity = 1
	BEGIN
		SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + 
@tableName + ' ON '' FROM ' + @tableName
		EXEC sp_executesql @sql
	END

--Build list of columns and values
SELECT @cols = @cols + ',' + '[' + column_name + ']', @vals = @vals + 
	'+'',''+ISNULL(master.dbo.fn_varbintohexstr(cast([' + 
column_name + '] as varbinary(max))),''NULL'')' 
FROM INFORMATION_SCHEMA.columns 
WHERE TABLE_NAME = @tableName and DATA_TYPE != 'timestamp'

--Build SQL string
SET @sql = 'SELECT ''INSERT INTO [' + @tableName + '] (' + 
SUBSTRING(@cols,2,LEN(@cols)) + ') ' + 
			'VALUES (''+' + SUBSTRING(@vals, 6, 
LEN(@vals)) + '+'')'' FROM ' + @tableName
--Adjust @whereClause and @orderByClause
IF LEN(@whereClause) > 0
	SET @sql = @sql + ' WHERE ' + @whereClause
IF LEN(@orderByClause) > 0
	SET @sql= @sql + ' ORDER BY ' + @orderByClause

--Execute SQL string
exec sp_executesql @sql

--Do we need 'SET IDENTITY_INSERT tableName OFF' statement?
IF @tableHasIdentity = 1
	BEGIN
		SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + 
@tableName + ' OFF '' FROM ' + @tableName
		EXEC sp_executesql @sql
	END

GO