I had a similar requirement.
In the script, enter instructions for each row in the table. Sometimes I need the Identification column, sometimes I didn’t.
So I wrote this:
CREATE PROCEDURE [dbo].[GenerateInsertScripts] (
@tableName varchar(100),
@tableSchema varchar(50) = 'dbo',
@skipIdentity bit = 1
)
AS
BEGIN
DECLARE @columnName varchar(800)
DECLARE @columnType varchar(20)
DECLARE @statementA varchar(MAX)
DECLARE @statementB varchar(MAX)
DECLARE @statement nvarchar(MAX)
DECLARE @isIdentity bit
DECLARE @commaFlag bit
SET @statementA = 'INSERT INTO [' + @tableSchema + '].[' + @tableName + '] ('
SET @statementB = ''' + '
DECLARE cols CURSOR FOR
SELECT
COLUMN_NAME,
DATA_TYPE,
(SELECT COLUMNPROPERTY(OBJECT_ID('[' + @tableSchema + '].[' + @tableName + ']'),
information_schema.columns.COLUMN_NAME, 'IsIdentity')) AS IsIdentity
FROM
information_schema.columns
WHERE
TABLE_NAME = @tableName
AND
TABLE_SCHEMA = @tableSchema
ORDER BY
ORDINAL_POSITION
OPEN cols
FETCH cols INTO @columnName, @columnType, @isIdentity
SET @commaFlag = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT (@isIdentity = 1 AND @skipIdentity = 1) BEGIN
IF @commaFlag = 1 BEGIN
SET @statementA = @statementA + ', '
SET @statementB = @statementB + ' + '', '' + '
END
SET @commaFlag = 1
SET @statementA = @statementA + '[' + @columnName + ']'
SET @statementB = @statementB + 'CASE WHEN [' + @columnName + '] IS NULL THEN ''NULL'' ELSE ' +
CASE
WHEN @columnType = 'bigint' OR @columnType = 'int' OR @columnType = 'tinyint' OR @columnType = 'bit' THEN
'CAST([' + @columnName + '] AS varchar(MAX))'
WHEN @columnType = 'datetime' THEN
''''''''' + CONVERT(varchar, [' + @columnName + '], 121) + '''''''''
ELSE
''''''''' + REPLACE(CAST([' + @columnName + '] AS varchar(MAX)), '''''''', '''''''''''') + '''''''''
END
+ ' END'
END
FETCH cols INTO @columnName, @columnType, @isIdentity
END
SET @commaFlag = 0
CLOSE cols
DEALLOCATE cols
SET @statementB = @statementB + ' + '''
SET @statement = 'SELECT ''' + @statementA + ') VALUES (' + @statementB + ')'' [Statement] FROM [' + @tableSchema + '].[' + @tableName + ']'
EXEC sp_executesql @statement
END