Generating Insert(s) with data from a table. (Where clause included)

=> Found this code online but it did not work as expected. This is a fixed code.

USE DATABASE_NAME
GO
begin tran t1

DECLARE @TableName SYSNAME
DECLARE @WhereClause VARCHAR(1024)
DECLARE @IdentityInsert INT
DECLARE @ColName SYSNAME
DECLARE @ColType TINYINT
DECLARE @ColStatus TINYINT
DECLARE @DebugMode BIT
DECLARE @ColList NVARCHAR(4000)
DECLARE @ValList NVARCHAR(4000)
DECLARE @SQL1 NVARCHAR(1000)
DECLARE @SQL2 NCHAR(10)
DECLARE @SQL3 NCHAR(4000)

–Your Table Name
SET @TableName = ‘TABLE_NAME’

— limit scope of inserts, this will be hard coded thing to narrow down the set
SET @WhereClause = ‘where CONDITION =VALUE’

— set to 1 if you only want a script
SET @DebugMode = 0

— set to 1 if you want to force IDENTITY_INSERT statements
SET @IdentityInsert = 0

SET @ColList = ”
SET @ValList = ”
SET @SQL1 = ‘SELECT REPLACE(”INSERT INTO ‘ + @TableName + ‘ (‘
SET @SQL2 = ‘) VALUES (‘
SET @SQL3 = ‘)” COLLATE DATABASE_DEFAULT, ”””NULL”””, ”NULL”) FROM ‘
+ @TableName

IF @DebugMode = 1
PRINT ‘– StmtShell: ‘ + @sql1 + @sql2 + @sql3

DECLARE csrColumns 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’)
AND c.name <> ‘IsLocReallyValid’
ORDER BY
ColID

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
Print @ColName
if @ColName <> ‘IsLocReallyValid’
BEGIN
if not ((@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0))
set @ColList = @ColList + ‘ ‘ + @ColName

if @ColType in (127,173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) — numeric types (nulls not supported yet)
if(@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0)
set @ValList = @ValList
else
set @ValList = @ValList + ‘ ”””+isnull(convert(varchar(200),[‘ + @ColName + ‘]),”null”)+”””’
else if @ColType in (175, 239, 231, 231, 167) — uid and string types
set @ValList = @ValList + ‘ ”””+isnull(replace([‘ + @ColName + ‘],””””,””””””),”null”)+”””’
else if @ColType in (36,40,42,58, 61) — dates , uniqueidentifiers(nulls not supported yet)
set @ValList = @ValList + ‘ ”””+isnull(convert(varchar(200),[‘ + @ColName + ‘]),”null”)+”””’

if @DebugMode = 1 begin print ‘– @ValList: ‘ + rtrim(@ValList) end
if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end — Check if column has Identity attribute
END
fetch next from csrColumns into @ColName, @ColType, @ColStatus
end
CLOSE csrColumns
DEALLOCATE csrColumns

SET @ColList = REPLACE(LTRIM(@ColList), ‘ ‘, ‘, ‘)
SET @ValList = REPLACE(LTRIM(@ValList), ‘ ‘, ‘, ‘)

IF @IdentityInsert = 1
AND @DebugMode = 1
PRINT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’
IF @IdentityInsert = 1
AND @DebugMode = 0
SELECT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’

IF @DebugMode = 1
PRINT @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ‘ ‘ + @WhereClause
ELSE
EXEC
(
@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ‘ ‘
+ @WhereClause
)

IF @IdentityInsert = 1
AND @DebugMode = 1
PRINT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’
IF @IdentityInsert = 1
AND @DebugMode = 0
SELECT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’

rollback tran t1