Generate INSERT for table with IDENTITY column

/***************************************************************************************************
Written by:			Jesse McLain
Purpose:			This spd is useful for generating INSERT statements to insert data into a table
					that has an identity column. What I specifically had in mind when I wrote it was
					to backup a table for testing, run the tests, and then if I needed to revert the
					table back to its original state, I could truncate it and then re-insert from the
					backup table. When using the Management Studio's scripting tasks to create the 
					insert statement, it wouldn't include the identity column. Also, you would have
					to add the "SELECT FROM" code to it. This spd basically combines all that into
					one call.
Input Parameters:	see below
Output Parameters:	printed results
Called By:			user
***************************************************************************************************/
CREATE PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]
	@Source_Table varchar(200),						-- req'd; the name of the source table 
	@Target_Table varchar(200) = '',				-- req'd; the name of the target table 
	@Print_Identity_Insert_Stmts char(1) = 'Y',		-- optional; 'Y' to include the SET IDENTITY_INSERT 
													-- statements in the output
	@Show_Matchless_Columns char(1) = 'N'			-- optional; 'Y' to include the columns from each 
													-- table that have no match in the other table (if 
													-- the tables have slightly different structures);
													-- these columns will be commented-out in output
AS

IF @Source_Table = 'help'
BEGIN
	PRINT 'PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]'
	PRINT '	@Source_Table varchar(200),						-- required; the name of the source table '
	PRINT '	@Target_Table varchar(200) = '''',				-- required; the name of the target table '
	PRINT '	@Print_Identity_Insert_Stmts char(1) = ''Y'',		-- optional; ''Y'' to include the SET IDENTITY_INSERT '
	PRINT '													-- statements in the output'
	PRINT '	@Show_Matchless_Columns char(1) = ''N''			-- optional; ''Y'' to include the columns from each '
	PRINT '													-- table that have no match in the other table (if '
	PRINT '													-- the tables have slightly different structures);'
	PRINT '													-- these columns will be commented-out in output'

	RETURN
END

DECLARE @sql_into varchar(8000)
DECLARE @sql_from varchar(8000)
DECLARE @sql varchar(8000)

DECLARE @crlf char(2)
SET @crlf = CHAR(13) + CHAR(10)

DECLARE @indent char(1)
SET @indent = CHAR(9)

DECLARE @ColName1 varchar(250)
DECLARE @ColOrder1 smallint
DECLARE @ColType1 varchar(50)
DECLARE @ColLength1 smallint
DECLARE @ColPrec1 smallint
DECLARE @ColScale1 smallint
DECLARE @IsNullable1 tinyint
DECLARE @ColName2 varchar(250)
DECLARE @ColOrder2 smallint
DECLARE @ColType2 varchar(50)
DECLARE @ColLength2 smallint
DECLARE @ColPrec2 smallint
DECLARE @ColScale2 smallint
DECLARE @IsNullable2 tinyint
DECLARE @OrderBy decimal(9,1)


DECLARE Table_Struct_Compare_Cursor CURSOR FOR
SELECT 
	ColName1,
	ColOrder1,
	ColType1,
	ColLength1,
	ColPrec1,
	ColScale1,
	IsNullable1,
	ColName2,
	ColOrder2,
	ColType2,
	ColLength2,
	ColPrec2,
	ColScale2,
	IsNullable2,
	OrderBy = ISNULL(CONVERT(decimal(9,1), ColOrder1), CONVERT(decimal(9,1), ColOrder2) + 0.5)
FROM 
	(SELECT DISTINCT
		ColName1 = C.Name,
		ColOrder1 = C.ColOrder,
		ColType1 = T1.Name,
		ColLength1 = C.Length,
		ColPrec1 = C.XPrec,
		ColScale1 = C.XScale,
		IsNullable1 = C.IsNullable
	FROM SysColumns C
	JOIN SysObjects O1 ON O1.Id = C.Id
	JOIN SysTypes T1 ON T1.XType = C.XType
	WHERE O1.Name = @Target_Table and T1.Name <> 'sysname') AS C1
FULL OUTER JOIN 
	(SELECT DISTINCT
		ColName2 = C.Name,
		ColOrder2 = C.ColOrder,
		ColType2 = T2.Name,
		ColLength2 = C.Length,
		ColPrec2 = C.XPrec,
		ColScale2 = C.XScale,
		IsNullable2 = C.IsNullable
	FROM SysColumns C
	JOIN SysObjects O2 ON O2.Id = C.Id
	JOIN SysTypes T2 ON T2.XType = C.XType
	WHERE O2.Name = @Source_Table and T2.Name <> 'sysname') AS C2
ON C2.ColName2 = C1.ColName1
ORDER BY OrderBy, ColOrder1, ColOrder2


-- init vars:
SET @sql_into = 'INSERT INTO ' + @Target_Table + ' ('
SET @sql_from = 'SELECT '
SET @sql = ''


OPEN Table_Struct_Compare_Cursor 

FETCH NEXT FROM Table_Struct_Compare_Cursor INTO 
	@ColName1,
	@ColOrder1,
	@ColType1,
	@ColLength1,
	@ColPrec1,
	@ColScale1,
	@IsNullable1,
	@ColName2,
	@ColOrder2,
	@ColType2,
	@ColLength2,
	@ColPrec2,
	@ColScale2,
	@IsNullable2,
	@OrderBy

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @ColName1 IS NULL 
	BEGIN
		IF @Show_Matchless_Columns = 'Y'
		BEGIN
			--SET @sql_into = @sql_into + @crlf + '--' + @indent + '<no matching column in ' + @Target_Table + ' for column ' + @ColName2 + '>,'
			SET @sql_into = @sql_into + @crlf + '--' + @indent + '<missing:' + @ColName2 + '>,'
			SET @sql_from = @sql_from + @crlf + '--' + @indent + '<' + @Source_Table + '.' + @ColName2 + '>,'
		END
	END
	ELSE IF @ColName2 IS NULL 
	BEGIN
		IF @Show_Matchless_Columns = 'Y'
		BEGIN
			SET @sql_into = @sql_into + @crlf + '--' + @indent + '<' + @Target_Table + '.' + @ColName1 + '>,'
			--SET @sql_from = @sql_from + @crlf + '--' + @indent + '<no matching column in ' + @Source_Table + ' for column ' + @ColName1 + '>,'
			SET @sql_from = @sql_from + @crlf + '--' + @indent + '<missing: ' + @ColName1 + '>,'
		END
	END
	ELSE IF @ColType1 <> @ColType2
	BEGIN
		SET @sql_into = @sql_into + @crlf + @indent + @ColName1 + ','
		SET @sql_from = @sql_from + @crlf + @indent + 'CONVERT(' + @ColType1 + ', ' + @ColName2 + '),'
	END
	ELSE
	BEGIN
		SET @sql_into = @sql_into + @crlf + @indent + @ColName1 + ','
		SET @sql_from = @sql_from + @crlf + @indent + @ColName2 + ','
	END


	FETCH NEXT FROM Table_Struct_Compare_Cursor INTO 
		@ColName1,
		@ColOrder1,
		@ColType1,
		@ColLength1,
		@ColPrec1,
		@ColScale1,
		@IsNullable1,
		@ColName2,
		@ColOrder2,
		@ColType2,
		@ColLength2,
		@ColPrec2,
		@ColScale2,
		@IsNullable2,
		@OrderBy
END

CLOSE Table_Struct_Compare_Cursor
DEALLOCATE Table_Struct_Compare_Cursor


SET @sql_into = LEFT(@sql_into, LEN(RTRIM(@sql_into)) - 1) + ')' + @crlf		-- remove trailing comma, add closing paren

SET @sql_from = LEFT(@sql_from, LEN(@sql_from) - 1) + @crlf		-- remove trailing comma
SET @sql_from = @sql_from + 'FROM ' + @Source_Table + @crlf


IF @Print_Identity_Insert_Stmts = 'Y'
	PRINT 'TRUNCATE TABLE ' + @Target_Table
	PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' ON'
PRINT @sql_into
PRINT @sql_from
IF @Print_Identity_Insert_Stmts = 'Y'
	PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' OFF'

PRINT ''
PRINT ''