Insert Script for MS SQL Server

Browsing the net i found this great script it creates a procedure with takes table name as input and returns a insert script for table works for both SQL Server 2000 and 2005. Great to use and no additional server requirement or installs required.

/***** Object: StoredProcedure [dbo].[sp_CreateDataLoadScript] Script
******/

SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ON

GO

Create Procedure[dbo].[sp_CreateDataLoadScript]@TblName
varchar(128)

as

/*execsp_CreateDataLoadScript 'MyTable'*/

create table #a (id intidentity (1,1), ColType int, ColName varchar(128))

insert #a
(ColType,ColName) select case when DATA_TYPE like '%char%' then 1 else 0 end
,COLUMN_NAME from information_schema.columns where TABLE_NAME =@TblNameorder by ORDINAL_POSITION

if not exists (select * from #a)
begin
raiserror('No columns found for table %s', 16,-1, @TblName)
return
end

declare @id int ,@maxid int,@cmd1 varchar(7000) ,@cmd2 varchar(7000)

select @id = 0 ,@maxid =max(id)from
#a

select @cmd1 = 'select '' insert ' + @TblName + ' ('select @cmd2 = ' + ''
select '' + 'while @id < @maxid begin select@id = min(id) from #a where id > @idselect

@cmd1 = @cmd1 + ColName +',' from #a where id = @idselect

@cmd2 = @cmd2+ ' casewhen ' + ColName + ' is null '+ ' then ''null'' '+ ' else '+
case when ColType = 1 then ''''''''' + ' + ColName + ' + '''''''''
else 'convert(varchar(20),' + ColName + ')' end+ ' end + '','' + 'from #a
where id = @idendselect

@cmd1 = left(@cmd1,len(@cmd1)-1) +' ) '' '

select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblNameselect '

/*' + @cmd1 + @cmd2 +'*/'

exec (@cmd1 + @cmd2)

droptable #a

Note: you will have to turn of identity column if you want to insert the primary key as well.