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 ''