Generate Insert Statements

Set NoCount On
Declare @TableList Table (tabname varchar(100), ExcludeDefault bit, ExcludeIdentity bit)

/*******************************************************************/
/*  Add or remove table information in this section before running.*/
/*  Insert a record into @TableList for each table you want output.*/
/*******************************************************************/
-- currently setup for Northwind.
Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values ('Orders',0,0)
Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values ('Products',0,0)
Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values ('Shippers',0,0)
--Note:  If ExcludeDefault is selected, this excludes any columns that have Default values declared.
--Note:  If ExcludeAuto is selected, this excludes any columns that are auto increment fields.
/*******************************************************************/
/*  Add or remove table information above before running.          */
/*  You can use the following script to generate the above list for*/
/*  the entire database.  Then you can cut and paste the rows you  */
/*  wish to modify                                                 */
/* 
   Select 'Insert Into @TableList (tabName, ExcludeDefault, ExcludeIdentity) Values (''' + name + ''',0,0)'
   From Sysobjects Where xtype = 'U'
   and (objectproperty(sysobjects.id, 'IsMsShipped') = 0)
*/
/*******************************************************************/

--declare some variables that will be used
DECLARE @InsertStmt varchar(8000),   -- Holds the Actual Insert Statement.
	@Fields varchar(8000),       -- Is the list of fields, comma delimited.
	@SelList varchar(8000),      -- A select statement to extract the data from the table.
	@ColName varchar(128),       -- One column's name.
	@IsChar tinyint,             -- Helps determine how to handle a particular column.
	@FldCounter int,             -- Loop Variable
	@TableData varchar(8000),    -- Contains a line of data to be inserted (comma delimited)
	@ExcludeDefault bit,         -- Retrieved from the table set above.
	@ExcludeIdentity bit         -- Retrieved from the table set above.

DECLARE CR_TableList CURSOR FAST_FORWARD FOR
SELECT tabName, ExcludeDefault, ExcludeIdentity From @TableList
FOR READ ONLY

DECLARE @table varchar(128)

OPEN CR_TableList
FETCH NEXT FROM CR_TableList INTO @table, @ExcludeDefault, @ExcludeIdentity
WHILE (@@fetch_status <> -1)
BEGIN
	--initialize some of the variables
	SELECT @InsertStmt = 'INSERT INTO ' + @Table + ' (',
		@Fields = '',
		@SelList = 'SELECT ',
		@FldCounter = 0
	
	--create a cursor that loops through the fields in the table
	--and retrieves the column names and determines the delimiter type that the
	--field needs
	DECLARE CR_Table CURSOR FAST_FORWARD FOR
	SELECT COLUMN_NAME,
		'IsChar' = CASE
			WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint') THEN 0
			WHEN DATA_TYPE in ('char', 'varchar','nvarchar','text','nchar','ntext' ) THEN 1
			WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
			ELSE 9
		END
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE table_name = @table
		AND DATA_TYPE <> 'timestamp'
		And (COLUMN_DEFAULT is Null or @ExcludeDefault = 0)
		And (columnproperty(object_id(@table), column_name,'IsIdentity') = 0 or @ExcludeIdentity = 0)
		ORDER BY ORDINAL_POSITION
	FOR READ ONLY
	
	
	OPEN CR_Table
	FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			IF @FldCounter = 0
			BEGIN
				SELECT @Fields = @Fields + @ColName + ', '
				SELECT @SelList = CASE
					WHEN @IsChar = 1 THEN @SelList + ' '''''''' + ISNULL( REPLACE('+ @ColName + ', '''''''', ''''''''''''),'''') + '''''''' +' + ' '
					WHEN @IsChar = 2 THEN @SelList + ' '''''''' + ISNULL(CONVERT(varchar(20),' + @ColName + '),''12/30/1899'') + '''''''' +' + ' '
					ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),'+@ColName + '),0)' + '+'''' + '
				END
	
				SELECT @FldCounter = @FldCounter + 1
	
				FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
			END

			If @@Fetch_Status <> -1
			begin
				SELECT @Fields = @Fields + @ColName + ', '
				SELECT @SelList = CASE
					WHEN @IsChar = 1 THEN @SelList + ' '','''''' + ISNULL(REPLACE(' + @ColName + ', '''''''',''''''''''''), '''') + '''''''' +' + ' '
					WHEN @IsChar = 2 THEN @SelList + ' '','''''' + ISNULL(CONVERT(varchar(20),' + @ColName + '),''12/30/1899'') + '''''''' +' + ' '
					ELSE @SelList + ' '','' + ISNULL(CONVERT(varchar(2000),'+@ColName + '),0)' + '+'
				END
			END

		END
		FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
	END
	CLOSE CR_Table
	DEALLOCATE CR_Table
	

	If @ExcludeIdentity = 0
	Begin
		Print 'Set Identity_Insert ' + @table + ' On'
		Print 'Go'
	End
	
	SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1)) -- Remove Trailing comma
	SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1)) -- Remove Trailing comma
	SELECT @SelList = @SelList + ' FROM ' + @table
	SELECT @InsertStmt = @InsertStmt + @Fields + ')'
	
	--now we need to create and load the temp table that will hold the data
	--that we are going to generate into an insert statement
	CREATE TABLE #TheData (TableData varchar(8000))
	INSERT INTO #TheData (TableData) EXEC (@SelList)
	
	--Cursor through the data to generate the INSERT statement / VALUES clause
	DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
	READ ONLY
	OPEN CR_Data
	FETCH NEXT FROM CR_Data INTO @TableData
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			PRINT @InsertStmt + ' VALUES (' + @TableData + ')' + char(13) + 'GO'
		END
		FETCH NEXT FROM CR_Data INTO @TableData
	END
	CLOSE CR_Data
	DEALLOCATE CR_Data
	DROP TABLE #TheData

	If @ExcludeIdentity = 0
	Begin
		Print 'Set Identity_Insert ' + @table + ' Off'
		Print 'Go'
	End

	FETCH NEXT FROM CR_TableList INTO @table, @ExcludeDefault, @ExcludeIdentity
END
CLOSE CR_TableList
DEALLOCATE CR_TableList