Scripts for Generating Table Data Insert Script

Taken from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53420

Go to the comments - modified code and change it to proc with tablename as a parameter

PROC UtilitiesGenerateInsertStatementsvarchar(100)AS BEGIN
--DECLARE @tableName varchar(100), --BEGIN PROC

DECLARE @execStr0 varchar(8000),@execStr1 varchar(8000),@execStr2 varchar(8000),@execStr3 varchar(8000),@execStr4 varchar(8000),@execStr5 varchar(8000),@execStr6 varchar(8000),@execStr7 varchar(8000),@execStr8 varchar(8000),@execStr9 varchar(8000)-- Display warning for unsupported typesIF EXISTS(SELECT *FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename AND DATA_TYPE NOT IN
('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 'money', 'bit', 'smallint', 'real', 'bigint'))BEGINSELECT DISTINCT DATA_TYPE + ' Type not supported'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename AND DATA_TYPE NOT IN
('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 'money', 'bit', 'smallint', 'real', 'bigint')END-- Build column translationsSELECT@execStr1 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr2 ELSE @execStr1 END,@execStr2 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr3 ELSE @execStr2 END,@execStr3 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr4 ELSE @execStr3 END,@execStr4 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr5 ELSE @execStr4 END,@execStr5 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr6 ELSE @execStr5 END,@execStr6 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr7 ELSE @execStr6 END,@execStr7 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr8 ELSE @execStr7 END,@execStr8 = CASE WHEN LEN(@execStr8) > 7500 THEN NULL ELSE @execStr8 END,@execStr8 = IsNull(@execStr8 + ' +' + CHAR(13) + CHAR(10),'') +CONVERT(varchar(8000),

CASE WHEN DATA_TYPE IN ('uniqueidentifier') THEN CHAR(9) + '''' + COLUMN_NAME + '=''+IsNull('''''''' + CONVERT(varchar(50),' + COLUMN_NAME + ') + '''''''',''null'')+'', '''
WHEN DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'sysname') THEN CHAR(9) + '''' + COLUMN_NAME + '=''+IsNull('''''''' + REPLACE(' + COLUMN_NAME + ','''''''','''''''''''') + '''''''',''null'')+'', '''
WHEN DATA_TYPE IN ('datetime') THEN CHAR(9) + '''' + COLUMN_NAME + '=''+IsNull('''''''' + CONVERT(varchar,' + COLUMN_NAME + ',121)+'''''''',''null'') + '', '''

WHEN DATA_TYPE IN ('tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 'money', 'bit', 'smallint', 'real', 'bigint') THEN CHAR(9) + '''' + COLUMN_NAME + '=''+IsNull(CONVERT(varchar,' + COLUMN_NAME + '),''null'')+'', ''' ELSE' ** DATA TYPE ' + DATA_TYPE + ' NOT SUPPORTED **'END)FROM INFORMATION_SCHEMA.COLUMNSWHERETABLE_NAME = @tablename AND DATA_TYPE IN
('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 'money', 'bit', 'smallint', 'real', 'bigint')ORDER BY ORDINAL_POSITIONSELECT
@execStr0 = 'Select ' + '''Insert Into ' + REPLACE(@tableName,' ','') + ' Select '' + ' + CHAR(13) + CHAR(10)SELECT @execStr1 = IsNull(@execStr1+', ', '')SELECT @execStr2 = IsNull(@execStr2+', ', '')SELECT @execStr3 = IsNull(@execStr3+', ', '')SELECT @execStr4 = IsNull(@execStr4+', ', '')SELECT @execStr5 = IsNull(@execStr5+', ', '')SELECT @execStr6 = IsNull(@execStr6+', ', '')SELECT @execStr7 = IsNull(@execStr7+', ', '')SELECT @execStr8 = left(@execStr8, len(@execStr8)-4) + ''''''SELECT @execStr9 = CHAR(13) + CHAR(10) + 'from [' + @tableName + ']'-- Comment in for Debug
-- Select @execStr0, @execStr1, @execStr2, @execStr3, @execStr4, @execStr5, @execStr6, @execStr7, @execStr8, @execStr9EXEC

(@execStr0 + @execStr1 + @execStr2 + @execStr3 + @execStr4 + @execStr5 + @execStr6 + @execStr7 + @execStr8 + @execStr9)END
--exec UtilitiesGenerateInsertStatements 'Product'





============================================================

This  one  is also good, uses coalesce
But not sure runs with newer version of sQL

Quite versatile

http://vyaskn.tripod.com/code/generate_inserts.txt
============================================================

http://www.dotnetspider.com/resources/543-Generate-INSERT-scripts-for-any-table.aspx

This was a great script. I've enhanced it to:
1. Generate statements for a single table or whole database
2. check for the existence of any data in the table before inserting
3. set identity insert on and off

Thanks:

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AUTHOR KARTHIK WITH MODIFICATIONS BY SYDNEY OSBORNE
DESCRIPTION BUILDS THE INSERT QUERY FOR ANY GIVEN TABLE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE SP_GEN_INSERT_SQL
@TABLENAME VARCHAR ( 2000 )
AS
BEGIN

DECLARE CUR_METADATA CURSOR FOR
SELECT NAME , XTYPE
FROM SYSCOLUMNS
WHERE ID IN ( SELECT ID FROM SYSOBJECTS WHERE NAME = @TABLENAME )
/* DECLARE VARIABLES*/
DECLARE @QRY1 VARCHAR (8000 ) /* Has column names */
DECLARE @QRY2 VARCHAR ( 8000 ) /* Has column values */

DECLARE @COLNAME VARCHAR ( 8000)
DECLARE @COLDATATYPE TINYINT
DECLARE @PRVCOLDATATYPE TINYINT

DECLARE @CHARPREFIX VARCHAR ( 5 )
DECLARE @CHARPOSTFIX VARCHAR ( 5 )
DECLARE @COLUMNSEPARATOR CHAR ( 1 )
DECLARE @NULLCHAR CHAR ( 4 )

SET @CHARPREFIX = ''''
SET @CHARPOSTFIX = ''''
SET @COLUMNSEPARATOR = ','
SET @NULLCHAR = 'NULL'

/* Start building the query */
SET @QRY1 ='SELECT ''INSERT INTO ' + @TABLENAME + '(' --+ " VALUES ( "

SET @QRY2 = ''

OPEN CUR_METADATA

FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE

WHILE @@FETCH_STATUS = 0
BEGIN
/* Add the Column names to the query */
SET @QRY1 = @QRY1 + @COLNAME + ','

/* Add Column values - Prefix & postfix the column values with quotes if they belong to any of the following datatypes */
/* TEXT - 35 | SMALLDATETIME - 58 | DATETIME - 61 | NTEXT - 99 | VARCHAR - 167 | CHAR - 175 | NVARCHAR - 231 | NCHAR - 239 */
IF ( @COLDATATYPE = 35 ) OR ( @COLDATATYPE = 58 ) OR ( @COLDATATYPE = 61 ) OR ( @COLDATATYPE = 99 ) OR
( @COLDATATYPE = 167 ) OR ( @COLDATATYPE = 175 ) OR ( @COLDATATYPE = 231 ) OR ( @COLDATATYPE = 239 )
BEGIN
IF @QRY2 = ''
BEGIN
SET @QRY2 = @QRY2 + @CHARPREFIX + @CHARPREFIX + @CHARPREFIX + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + '''$$$$''' + ')' --+ "'')"
END
ELSE
BEGIN
SET @QRY2 = @QRY2 + @CHARPREFIX + @CHARPREFIX + @CHARPREFIX + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + '''$$$$''' + ')' --+ "'')"
END
SET @QRY2 = @QRY2 + '+' + @CHARPOSTFIX + @CHARPOSTFIX + @CHARPOSTFIX + @COLUMNSEPARATOR
END
ELSE /*If the column is a number dont prefix/postfix quotes */
BEGIN
SET @QRY2 = @QRY2 + '''' + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + '''$$$$''' + ')'
SET @QRY2 = @QRY2 + '+' + @CHARPOSTFIX + @COLUMNSEPARATOR
END
--PRINT @QRY2
SET @PRVCOLDATATYPE = @COLDATATYPE
FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE
END

CLOSE CUR_METADATA
DEALLOCATE CUR_METADATA

SET @QRY1 = SUBSTRING ( @QRY1 , 1 , LEN ( @QRY1 ) - 1 )
SET @QRY1 = @QRY1 + ')VALUES(' --+ "'" + "'" +"'"

SET @QRY2 = SUBSTRING ( @QRY2 , 1 , LEN ( @QRY2 ) - 1 )
SET @QRY2 = @QRY2 + ')' + @CHARPOSTFIX
SET @QRY2 = @QRY2 + ' FROM ' + @TABLENAME

/* Create a temporary table to hold the result of the query - i.e. it will have the INSERT statements */
--CREATE TABLE SQLTRACE ( SQL VARCHAR ( 8000 ) )
--print @QRY1 + @QRY2
SET @QRY2 = ' INSERT INTO SQLTRACE ' + @QRY1 + @QRY2
IF LEN ( @QRY2 ) < 8000
BEGIN
SET NOCOUNT ON
--PRINT @QRY2
EXEC ( @QRY2 )
--PRINT ' INSERT INTO SQLTRACE ' + @QRY1 + @QRY2
--EXEC ( ' INSERT INTO SQLTRACE ' + @QRY1 + @QRY2 )
UPDATE SQLTRACE SET SQL = REPLACE ( SQL , '''$$$$''' , @NULLCHAR )
SELECT * FROM SQLTRACE
END
ELSE
BEGIN
PRINT 'UNABLE TO GENERATE INSERT STATEMENTS FOR TABLE :' +@TABLENAME
END
SET NOCOUNT OFF
END /* END OF FILE */
============================================================ --END PROC
ALTER
@tableName