Migrating Data
When migrating between Courion environments I often run into situations where I need to not only migrate the structure of a table, but also the data. This is most common when migrating configuration tables. I looked for some SQL that would generate insert statements from a given table, and came across this blog entry.
This solution was not perfect, as it didn’t properly handle columns with spaces or that used reserved words in their names. Luckily someone else had fixed this and placed the solution in the comments. There is still one drawback that it handles empty columns as NULLs, but it still handles 90% of the work. If I have the time, I’ll try to work that out and post an updated solution.
SQL Code and usage after the jump.
Usage:
Past the code below in Query Analyzer, replacing the line
and name like 'AMEX%'
with the appropriate where clause to find your tables. You should then be able to run it and it will generate your SQL.
SQL Code:
create table #tmp (
SQLText varchar(8000) )
create table #tmp2 (
Id int identity,
SQLText varchar(8000) )
set nocount on
delete #tmp
delete #tmp2
declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)
declare csrTables cursor for
select name
from sysobjects
where type in ('u')
and name like 'AMEX%'
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,[' + sc.name + ']),''null'')+'',''+'
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID
select @vsCols = @vsCols + quotename(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
go
drop table #tmp
drop table #tmp2
