Click here to Skip to main content
15,860,943 members
Articles / Programming Languages / T-SQL
Alternative
Tip/Trick

INSERT script from Microsoft SQL table data

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
6 Jul 2011CPOL 7.2K   2
IF OBJECT_ID('sysGenerateInsert','P') IS NOT NULLBEGIN DROP PROC sysGenerateInsert PRINT 'sysGenerateInsert SP successfully dropped'END GO CREATE PROC sysGenerateInsert(@TblName varchar(128))ASBEGIN ----------------------------------------------------------------- --...
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
	--
	-- Added SQL Server 2000 compatibility:
	-- David Young, MCSE, MCDBA		On: 6-Jul-2011
	--              http://www.linkedin.com/in/jdavidyoung
	-----------------------------------------------------------------
	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(8000),
			@InsertIntoData VARCHAR(8000)
 
	SELECT @InsertIntoStmt = 'SELECT '' INSERT INTO ' + @TblName + ' ( '
	SELECT @InsertIntoData = '(''+' 
 
	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 + @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)


Written By
Software Developer (Senior) NRG Energy, Inc.
United States United States
I have fifteen years' experience as an information technology generalist with several Fortune 500 companies. My expertise includes systems architecture, Web, database, and application development, application support, and customer relations. I am a graduate of Texas A&M University with a degree in management information systems.

Comments and Discussions

 
GeneralReason for my vote of 4 Goodone Pin
ramakrishnankt7-Jul-11 3:56
ramakrishnankt7-Jul-11 3:56 
GeneralI have modified the script above to add SQL Server 2000 comp... Pin
Deep In The Code6-Jul-11 4:52
professionalDeep In The Code6-Jul-11 4:52 

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.