Generate INSERT statements from SQL Server

The original version of this code comes from here. That version, however, didn’t handle NULLs and just inserted empty strings or zeros. In the comments there was an updated version by spider_pat that supported NULL values but had a couple of problems, namely it only had one column to INSERT into (due to placing code inside of a loop instead of outside) and it didn’t enclose columns in brackets. The first was really easy to fix and the second most people probably don’t care about unless you have some system that automatically creates table names and you can’t rename them. Also, I took a little bit of time to clean up the code to make it more readable. Yes, I removed BEGIN/END blocks from single-statement IFs which I generally recommend against but this made it much easier to read.

CREATE PROC InsertGenerator
(
@tableName varchar(100)
)
AS
/*
Code from  http://www.codeproject.com/KB/database/InsertGeneratorPack.aspx
*/
--Declare a  cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type,table_schema FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string         nvarchar(max) --for storing the first  half of INSERT statement
DECLARE @stringData     nvarchar(max) --for storing the data  (VALUES) related statement
DECLARE @dataType nvarchar(max) --data types returned for  respective columns
DECLARE @schemaType     nvarchar(max) --table schema value for  correct schema execution
SET @stringData=''
DECLARE @colName nvarchar(max)
FETCH NEXT FROM cursCol INTO @colName, @dataType, @schemaType
IF @@fetch_status<>0
BEGIN
      PRINT 'Table  '+@tableName+' not found, processing skipped.'
      CLOSE curscol
      DEALLOCATE curscol
      RETURN
END
SET @string='INSERT ['+@schemaType+'].['+@tableName+']('
WHILE @@FETCH_STATUS=0
BEGIN
      IF @dataType in ('varchar','char','nchar','nvarchar')
            SET @stringData = @stringData + ''''' + ISNULL('''''''' + REPLACE([' + @colName + '],'''''''','''''''''''') + '''''''', ''NULL'') + '',''+'
      ELSE IF @dataType in ('text','ntext')
            SET @stringData = @stringData + '''''''''+ISNULL(cast([' + @colName + '] as  varChar(max)),'''')+'''''',''+'
      ELSE IF @dataType = 'money'
            SET @stringData = @stringData+'''CONVERT(money,''''''+ISNULL(CAST([' + @colName + '] as varChar(max)),''0.0000'')+''''''),''+'
      ELSE IF @dataType='datetime'
            SET @stringData = @stringData + '''CONVERT(datetime,' + '''+ISNULL(''''' + '''''+CONVERT(varChar(max),[' + @colName + '],121)+''''' + ''''',''NULL'')+'',121),''+'
      ELSE IF @dataType='image'
            SET @stringData = @stringData + '''''''''+ISNULL(CAST(CONVERT(varBinary,[' + @colName + ']) as varChar(6)),''0'')+'''''',''+'
      ELSE
            SET @stringData = @stringData + '''' + '''+ISNULL(''''' + '''''+CONVERT(varChar(max),[' + @colName + '])+''''' + ''''',''NULL'')+'',''+'
      SET @string=@string + '[' + REPLACE(@colName,'''','''''') + '],'
      FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@schemaType+'.'+@tableName
PRINT @query
EXEC sp_executesql @query
--select @query
CLOSE cursCol
DEALLOCATE cursCol
GO

UPDATE 12/21/2010

Fixed a bug where content with single quotes was breaking.