Generate Insert Multiple Records Using One Insert Statement – Use of UNION ALL

Version I

DECLARE @TableName            VARCHAR(50)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @InsColNames AS       VARCHAR(8000)
DECLARE @NewLineChar AS       VARCHAR(2)
DECLARE @HorizontalTabChar AS VARCHAR(1)
DECLARE @SQuoteFront AS       VARCHAR(15)
DECLARE @SQuoteBack AS        VARCHAR(15)
DECLARE @Comma AS             VARCHAR(150)

SET @SQuoteFront       = '''''''''+'
SET @SQuoteBack        = '+'''''''''
SET @Comma             = ''',''' + ', '
SET @TableName         = 'NetworkPath'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)

SELECT   @ColNames     = ( COALESCE (@ColNames + ( '''' + column_name + '''' + ', ' ), '') ),
         @InsColNames  = (
                  CASE
                           WHEN
                                    (
                                             data_type = 'bit'
                                    OR       data_type = 'bigint'
                                    OR       data_type = 'decimal'
                                    OR       data_type = 'float'
                                    OR       data_type = 'int'
                                    OR       data_type = 'money'
                                    OR       data_type = 'numeric'
                                    OR       data_type = 'tinyint'
                                    OR       data_type = 'image'
                                    )
                           THEN COALESCE (@InsColNames, '') + ( 'CAST( ' + column_name  + ' AS VARCHAR)' + ' + ' + '''' + ' AS ' + column_name + ''''   + ', ' + '''' + ',' + '''' + ',' )
                           ELSE COALESCE (@InsColNames, '') + ( @SQuoteFront + 'CAST( ' + column_name + ' AS VARCHAR('+ CAST(character_maximum_length AS VARCHAR)  +') )' + @SQuoteBack + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + @Comma )
                  END )
FROM     information_schema.columns
WHERE    table_name = @TableName
ORDER BY ordinal_position ASC

--Remove comma(,) at the end
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)

--PRINT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )'
--Generate Inserte statement for the given table
SELECT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )' AS InsertStatement

--PRINT 'SELECT '+  @InsColNames + ' FROM ' + @TableName

--Remove ,',', at the end
SET @InsColNames = LEFT(@InsColNames, LEN(@InsColNames) - 5)
PRINT CHAR(13) + CHAR(10)

SELECT @InsColNames

/*
PRINT 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames +
''' UNION ALL' +
'''' + CHAR(13) + CHAR(10) + 'FROM ' + @TableName
*/

EXEC( 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames + ''' UNION ALL' + '''' + 'FROM ' + @TableName )

Version II

DECLARE @TableName            VARCHAR(50)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @InsColNames AS       VARCHAR(8000)
DECLARE @NewLineChar AS       VARCHAR(2)
DECLARE @HorizontalTabChar AS VARCHAR(1)
DECLARE @SQuoteFront AS       VARCHAR(15)
DECLARE @SQuoteBack AS        VARCHAR(15)
DECLARE @Comma AS             VARCHAR(150)
SET @SQuoteFront       = '''''''''+'
SET @SQuoteBack        = '+'''''''''
SET @Comma             = ''',''' + ', '
SET @TableName         = 'NetworkPath'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)
SELECT   @ColNames     = ( COALESCE (@ColNames + ( '''' + column_name + '''' + ', ' ), '') ),
         @InsColNames  = (
         CASE
                  WHEN
                           (
                                    data_type = 'bit'
                           OR       data_type = 'bigint'
                           OR       data_type = 'decimal'
                           OR       data_type = 'float'
                           OR       data_type = 'int'
                           OR       data_type = 'money'
                           OR       data_type = 'numeric'
                           OR       data_type = 'tinyint'
                           OR       data_type = 'image'
                           )
                  THEN COALESCE (@InsColNames, '') + ( 'CAST( ' + column_name + ' AS VARCHAR)' + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + '''' + ',' + '''' + ',' )
                  ELSE COALESCE (@InsColNames, '') + ( @SQuoteFront + 'CAST( ' + column_name + ' AS VARCHAR('+ CAST(character_maximum_length AS VARCHAR) +') )' + @SQuoteBack + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + @Comma )
         END )
FROM     information_schema.columns
WHERE    table_name = @TableName
ORDER BY ordinal_position ASC
--Remove comma(,) at the end
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)
--PRINT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )'
--Generate Inserte statement for the given table
SELECT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )' AS InsertStatement
--PRINT 'SELECT '+  @InsColNames + ' FROM ' + @TableName
--Remove ,',', at the end
SET @InsColNames = LEFT(@InsColNames, LEN(@InsColNames) - 5)
PRINT CHAR(13)                                          + CHAR(10)
SELECT @InsColNames
/*
PRINT 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames +
''' UNION ALL' +
'''' + CHAR(13) + CHAR(10) + 'FROM ' + @TableName
*/
EXEC( 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames + ''' UNION ALL' + '''' + 'FROM ' + @TableName )

Reference