You can accomplish the same task using a set based approach which should perform considerably quicker.

It does work with text and ntext data types, but not image. I have no idea what will happen if geography, geometry or hierarchyid data types are used, you have been warned!

DECLARE @SchemaName NVARCHAR(128)

, @TableName NVARCHAR(128);

SELECT @SchemaName = 'dbo'

, @TableName = 'MyTable';

IF ((

SELECT COUNT(1)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

AND TABLE_SCHEMA = @SchemaName

AND DATA_TYPE IN ('image')

) > 0)

BEGIN

RAISERROR('Error: The table contains an IMAGE data type, this is unsupported', 18, 1);

RETURN;

END

DECLARE @ColumnNames NVARCHAR(MAX)

, @ColumnValues NVARCHAR(MAX)

, @Cmd NVARCHAR(MAX);

SELECT @ColumnNames =

STUFF((

SELECT ', ' + QUOTENAME(COLUMN_NAME)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

AND TABLE_SCHEMA = @SchemaName

ORDER BY ORDINAL_POSITION

FOR XML PATH('')

),1,1,'');

SELECT @ColumnValues =

STUFF((

SELECT ', '' + COALESCE(' +

CASE WHEN DATA_TYPE IN ('tinyint','smallint','int','real','money','float','bit','decimal','numeric','smallmoney','bigint') THEN '''''' ELSE '''''''''' END +

' + CAST(' + COLUMN_NAME + ' AS NVARCHAR(MAX)) + ' +

CASE WHEN DATA_TYPE IN ('tinyint','smallint','int','real','money','float','bit','decimal','numeric','smallmoney','bigint') THEN '''''' ELSE '''''''''' END +

', ''NULL'') + '''

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

AND TABLE_SCHEMA = @SchemaName

ORDER BY ORDINAL_POSITION

FOR XML PATH('')

),1,1,'');

SET @Cmd =

'SELECT

''INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' (' + @ColumnNames + ') VALUES (' + @ColumnValues + ')''

FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);

PRINT 'Column Names:

' + @ColumnNames;

PRINT '

Column Values:

' + @ColumnValues;

PRINT '

Command:

' + @Cmd;

EXEC sp_executesql @Cmd;