Generate insert statements with column names for any or all tables

--Example: For every table in the current database, generate a truncate/insert
exec sp_MSforeachtable @command1="exec [spd_Build_Insert_Into_Stmt_With_Identity] '?','?','[LINKEDSERVERNAME].DATABASENAME.'"

/***************************************************************************************************
Originally written by:    Jesse McLain
Updated by:                Darren Gordon
***************************************************************************************************/

alter 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 
    @FromPrefix varchar(500)='',                    -- String that will be added to the from statement, this lets you use linked server data sources
    @Print_Truncate char(1) = 'Y',                    -- optional; 'Y' to print truncate statement
    @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
    @AddGo char(1) = 'Y'                                    -- Add a GO to the end which will allow you to view progress when a batch is running
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 ' @FromPrefix varchar(500)='''',                    -- String that will be added to the from statement, this lets you use linked server data sources '
    PRINT ' @Print_Truncate char(1) = ''Y'',                -- optional; ''Y'' to print truncate statement '
    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'
    PRINT ' @AddGo char(1) = ''Y''                            -- Add a GO to the end which will allow you to view progress when a batch is running'

    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 = REPLACE(REPLACE(REPLACE(@Target_Table,'[dbo].',''),'[',''),']','') 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 = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']','') 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 ' + @FromPrefix + @Source_Table + ';'+ @crlf

PRINT 'select ''Started Populating: ' + @Target_Table + '''+ CONVERT(varchar,GETDATE(),108) ;'
IF @AddGo = 'Y'
    PRINT 'GO '
IF @Print_Truncate = 'Y'
    PRINT 'TRUNCATE TABLE ' + @Target_Table

--If the table does not have an identity column, then do not print the set identity_insert statement which would cause an error
if NOT exists (select 1 from sys.columns sc inner join sys.objects so on so.Object_Id = sc.Object_Id where sc.is_identity = 1 and so.Type = 'u' and so.Name = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']','')) 
    set @Print_Identity_Insert_Stmts = 'N'

IF @Print_Identity_Insert_Stmts = 'Y'
    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 ''