Click here to Skip to main content
15,312,009 members
Articles / Programming Languages / T-SQL
Tip/Trick
Posted 4 Jan 2011

Tagged as

Stats

19.1K views
2 bookmarked

INSERT script from Microsoft SQL table data

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
5 Jan 2011CPOL
The trick shows how to generate the INSERT script for each line of 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.

SQL
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'

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

GuruprasadV
Technical Lead Microsoft
India India
f

Comments and Discussions

 
BugGives error Insufficient result space to convert uniqueidentifier value to char. Pin
Jafin24-Sep-14 15:59
MemberJafin24-Sep-14 15:59 
Attempting to run Script on MSSQL2008r2 with a table with uniqueidentifier gives error
"Insufficient result space to convert uniqueidentifier value to char.
GeneralA little text around the same would be helpful. Pin
Sandeep Mewara2-Jan-11 21:52
mveSandeep Mewara2-Jan-11 21:52 
Questiondsadasd Pin
vaibjcady11-Jul-11 18:21
Membervaibjcady11-Jul-11 18:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.