Click here to Skip to main content
Click here to Skip to main content
Alternative Tip/Trick

Tagged as

INSERT script from Microsoft SQL table data

, 6 Jul 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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)

Share

About the Author

Deep In The Code
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.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralReason for my vote of 4 Goodone Pinmemberramakrishnankt7-Jul-11 4:56 
GeneralI have modified the script above to add SQL Server 2000 comp... PinmemberMember 21683816-Jul-11 5:52 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 6 Jul 2011
Article Copyright 2011 by Deep In The Code
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid