INSERT script from Microsoft SQL table data

There are times you want to create an INSERT statement for all the rows of a table. The SSMS may help you to generate INSERT statement template. But there is no way to generate INSERT statement for all the rows. The script below will help you to convert your table data to an INSERT statement.

This is useful when you want to create a script for configuration data / meta data lookup.

IF OBJECT_ID('sysGenerateInsert','P') IS NOT NULL
BEGIN
	DROP PROC sysGenerateInsert
	PRINT 'sysGenerateInsert SP successfully dropped'
END	
GO

CREATE PROC sysGenerateInsert(@TblName varchar(128))
AS
BEGIN
	-----------------------------------------------------------------
	-- Purpose: Generates INSERT statement for the given table 
	--          * Multiline insert
	--          * Copy the generated Script and remove the last comma
	--            and execute
	--
	-- Usage:   EXEC sysGenerateInsert 'TableName'
	-- 
	-- Created By: Guruprasad    On: 3-Jan-2010
	-----------------------------------------------------------------
	CREATE TABLE #ColumnMetaData (
		Id		INT IDENTITY (1,1), 
		IsChar	INT, 
		ColName VARCHAR(128)
	)

	IF NOT EXISTS (SELECT * 
	                 FROM INFORMATION_SCHEMA.COLUMNS
	                WHERE  TABLE_NAME = @TblName)
	BEGIN
		RAISERROR('No columns found for table %s or Table not exist', 16,-1, @TblName)
		RETURN
	END

	INSERT 	#ColumnMetaData (IsChar, ColName)
	SELECT CASE WHEN DATA_TYPE LIKE '%char%' 
	            THEN 1 
	            ELSE 0 END IsChar,
		   COLUMN_NAME     ColName
	FROM   INFORMATION_SCHEMA.COLUMNS
	WHERE  TABLE_NAME = @TblName
	ORDER BY ORDINAL_POSITION
	
    DECLARE	@InsertIntoStmt VARCHAR(MAX),
			@InsertIntoData VARCHAR(MAX)

	SELECT @InsertIntoStmt = 'SELECT '' INSERT INTO ' + @TblName + ' ( '
	SELECT @InsertIntoData = 'SELECT ''(''+' 

	SELECT 	@InsertIntoStmt = @InsertIntoStmt + ColName + ','
	FROM	#ColumnMetaData

	SELECT @InsertIntoData = 	@InsertIntoData
			+ ' CASE WHEN ' + ColName + ' IS NULL '
			+	' THEN ''NULL'' '
			+	' ELSE '
			+	  CASE WHEN IsChar = 1 
			           THEN  ''''''''' + ' + ColName + ' + ''''''''' 
			           ELSE 'CONVERT(VARCHAR(20),' + ColName + ')' 
			      END
			+ ' END + '','' + '
	FROM	#ColumnMetaData
	
	SELECT @InsertIntoStmt = LEFT(@InsertIntoStmt,LEN(@InsertIntoStmt)-1) + ' )  VALUES '''	
	SELECT @InsertIntoData = LEFT(@InsertIntoData,LEN(@InsertIntoData)-8) + ' + ''),'' FROM ' + @tblName

	EXEC (@InsertIntoStmt + ' UNION ALL ' + @InsertIntoData)
	
	DROP TABLE #ColumnMetaData
END
go

PRINT 'sysGenerateInsert SP successfully created'