SQL Script to generate INSERT Statement

DECLARE @TableName sysName
DECLARE @WhereClause VarChar(1024)
DECLARE @ColumnList NVarChar(4000)
DECLARE @ColumnName SysName
DECLARE @ColumnType TinyInt
DECLARE @ColumnStatus TinyInt
DECLARE @DebugMode Bit
DECLARE @IdentityInsert Int
DECLARE @ValueList NVarChar(4000)
DECLARE @String1 NVarChar(1000)
DECLARE @String2 NChar(10)
DECLARE @String3 NChar(1000)
SET @TableName = '' -- Add table name within single quote 
SET @WhereClause = '' -- limit scope of inserts
SET @DebugMode = 0 -- SET to 1 if you only want a script
SET @IdentityInsert = 0 -- SET to 1 if you want to force IDENTITY_INSERT statements


SET @ColumnList = ''
SET @ValueList = ''
SET @String1 = 'SELECT REPLACE(''INSERT INTO ' + @TableName + ' ('
SET @String2 = ') VALUES ('
SET @String3 = ')'', ''''''null'''''', ''null'') FROM ' + @TableName
IF @DebugMode = 1 PRINT '-- StmtShell: ' + @String1 + @String2 + @String3
DECLARE curColumns CURSOR LOCAL FAST_FORWARD FOR
SELECT C.Name, C.XType, C.Status
FROM SysColumns C
INNER JOIN SysObjects O
ON O.ID = C.ID
WHERE O.Name = @TableName
AND O.XType IN ('U', 'S')
ORDER BY ColID


OPEN curColumns
FETCH NEXT FROM curColumns INTO @ColumnName, @ColumnType, @ColumnStatus
WHILE @@fetch_status = 0
BEGIN
SET @ColumnList = @ColumnList + ' ' + @ColumnName
IF @ColumnType IN (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet)
SET @ValueList = @ValueList + ' ''+CONVERT(VarChar(200),' + @ColumnName + ')+'''
ELSE IF @ColumnType IN (175, 239, 231, 231, 167) -- uid and string types


SET @ValueList = @ValueList + ' ''''''+ISNULL(' + @ColumnName + ',''null'')+'''''''


ELSE IF @ColumnType IN (58, 61) -- dates (nulls not supported yet)


SET @ValueList = @ValueList + ' ''''''+CONVERT(VarChar(200),' + @ColumnName + ')+'''''''


ELSE IF @ColumnType = 36 -- uniqueidentfiers (nulls not supported yet)


SET @ValueList = @ValueList + ' ''''{''+CONVERT(VarChar(200),' + @ColumnName + ')+''}'''''


IF @DebugMode = 1 BEGIN PRINT '-- @ValueList: ' + RTRIM(@ValueList) END 


IF (@ColumnStatus & 0x80) = 0x80 BEGIN SET @IdentityInsert = 1 END -- Check if column has Identity attribute


FETCH NEXT FROM curColumns INTO @ColumnName, @ColumnType, @ColumnStatus


END
CLOSE curColumns
DEALLOCATE curColumns


SET @ColumnList = REPLACE(LTRIM(@ColumnList), ' ', ', ')
SET @ValueList = REPLACE(LTRIM(@ValueList), ' ', ', ')
IF @IdentityInsert = 1
PRINT 'SET IDENTITY_INSERT ' + @TableName + ' ON'


IF @DebugMode = 1
PRINT @String1 + @ColumnList + @String2 + @ValueList + @String3 + ' ' + @WhereClause
ELSE
EXEC (@String1 + @ColumnList + @String2 + @ValueList + @String3 + ' ' + @WhereClause)


IF @IdentityInsert = 1
PRINT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'