Generate INSERT statements from existing data in sql server 2005

This is very common requirement in database to move data from one server to another server. I saw many of stored procs but they are not running well at large data. So I modified one of them which was in SQL Server 2000. I updated it for SQL Server 2005.
Run Following stored proc and Enjoy it!!!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[spInsertScriptGenerator]
(
@p_tableName varchar(255)
)
as
Begin

Declare @tmp table
(
SQLText varchar(Max)
)
Declare @tmp2 table
(
Id int identity,
SQLText varchar(MAX)
)
set nocount on
declare @vsSQL varchar(Max),
@vsCols varchar(Max),
@vsTableName varchar(100)
declare csrTables cursor for
select name from sysobjects where type in ('u')and name =@p_tableName
order by name

open csrTables
fetch next from csrTables into @vsTableName
while (@@fetch_status = 0)

begin
select @vsSQL = '',@vsCols = ''
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
when sc.type = 35 then

'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' + '+'''''',''+'
else
'isnull(convert(varchar(100),' + sc.name + '),''null'')+'',''+'
end
from syscolumns sc where sc.id = object_id(@vsTableName)
order by ColID

select @vsCols = @vsCols + sc.name + ','from syscolumns sc
where sc.id = object_id(@vsTableName) order by ColID

select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)
select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)

insert @tmp
exec ('select ' + @vsSQL + ' from ' + @vsTableName)

update @tmp
set sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values(' + substring(sqltext,1,datalength(sqltext)-1) + ')'

insert @tmp2 select 'DELETE from ' + @vsTableName
insert @tmp2 values ('GO')

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert @tmp2 select 'set identity_insert ' + @vsTableName + ' on'
end

insert @tmp2 select * from @tmp

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1

begin
insert @tmp2
select 'set identity_insert ' + @vsTableName + ' off'
end

insert @tmp2 values ('GO')
insert @tmp2

select 'update statistics ' + @vsTableName
insert @tmp2 values ('GO')

delete @tmp
fetch next from csrTables into @vsTableName
end

close csrTables
deallocate csrTables

update @tmp2
set sqltext = substring(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
where not(charindex(',)',sqltext) = 0)

update @tmp2
set sqltext = replace(sqltext, ',''''',',null')
where not (charindex(',''''',sqltext) = 0)

update @tmp2
set sqltext = replace(sqltext, '(''''',',null')
where not (charindex('(''''',sqltext) = 0)

set nocount off
select sqltext from @tmp2 order by id

end

No comments:

Post a Comment