SQL Generate Insert script for pretty much any table ...

I am pretty proud of this code ... I made it a while ago, but still use it pretty often.
What does it do?
generates insert script for each row in a table ...
Let's say you create a table, you insert some data, and you'd like to generate a script to put the table and the data in an other database ... well ... you first script your table ... and then you run: sp_GenerateInsert 'YourTableNameHere'

(this is a stored procedure, and the result is only printing a string on the screen - you can run it whenever you want - won't harm your database structure OR data)

and that is it !

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GenerateInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GenerateInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


CREATE procedure sp_GenerateInsert
@TableName varchar(50)
as
Begin

-- Utility stored Procedure
-- Returns the list with inserts (re-create the table with all the data)

print '-- Swoosh 2004'
print ''
print 'SET IDENTITY_INSERT ' + @TableName + ' ON'
print 'GO'
print ''

declare @strSQL varchar(8000)
declare @ColumnList varchar(8000)
declare @ColumnListInOne varchar(8000)
declare @ColumnName varchar(8000)
declare @ColumnType int
declare @ID as integer

select @ID = id from sysobjects where type = 'U' and name = @TableName
set @ColumnList = ''
set @ColumnListInOne = ''

declare ccursor cursor for
select Name, Xtype
from syscolumns
where id = @ID

open ccursor
fetch next from ccursor into @ColumnName, @ColumnType
while @@FETCH_STATUS = 0
begin

Select @ColumnList = @ColumnList + @ColumnName + ', '

if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float'))
begin
Select @ColumnListInOne = @ColumnListInOne
Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''{|SWOOSH_NULL_REPLACER|}''),'''''''','''''''''''')))'
end
else
begin
Select @ColumnListInOne = @ColumnListInOne + '''''''''' + ' + '
Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''''),'''''''','''''''''''')))'
end

if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float'))
begin
Select @ColumnListInOne = @ColumnListInOne
end
else
begin
Select @ColumnListInOne = @ColumnListInOne + ' + ' + ''''''''''
end

Select @ColumnListInOne = @ColumnListInOne + ' + '', '' + '

fetch next from ccursor into @ColumnName, @ColumnType
end --while @@FETCH_STATUS = 0
close ccursor

deallocate ccursor

set @ColumnList = Left(@ColumnList,len(@ColumnList)-1)
set @ColumnListInOne = Left(@ColumnListInOne,len(@ColumnListInOne)-9)

set @strSQL = ''
set @strSQL = @strSQL + ' declare @AllValues varchar(8000)'
set @strSQL = @strSQL + ' set @AllValues = '''''
set @strSQL = @strSQL + ' declare ccursor cursor for '
set @strSQL = @strSQL + ' select ' + @ColumnListInOne + ' from ' + @TableName
set @strSQL = @strSQL + ' open ccursor '
set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
set @strSQL = @strSQL + ' while @@FETCH_STATUS = 0 '
set @strSQL = @strSQL + ' begin '
set @strSQL = @strSQL + ' print ''insert into ' + @TableName + ' (' + @ColumnList + ') values ('' + replace(@AllValues, ''{|SWOOSH_NULL_REPLACER|}'', ''NULL'') + '')'' '
set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
set @strSQL = @strSQL + ' end '
set @strSQL = @strSQL + ' close ccursor '
set @strSQL = @strSQL + ' deallocate ccursor '
exec (@strSQL )

print ''
print 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
print 'GO'

End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Have fun with it ...
and if you come up with some cool "extension" / "upgrade" to it ... tell me about it ;)
p.s. there is an other version, that doesn't include the first column (generates the insert script without the Identity column ... which is also usable ...)