 05-30-2007, 6:53 AM
|
devzero
Joined on 05-29-2007
Posts 1
|
Migrating content from several BA 3.0 to one BA 5.0
|
  
|
|
|
Quick and dirty hack, but it works. You of course have quite a bit of manual tidying upp to do afterwards but it does save a lot of work even so. Don't do this into any form of database you are even remotely afraid of :D, to import the attached files you have to copy the dirs into a temp directory and modify the paths in this script. Remember to commit tran when you feel everything is working, also remember that the moving of files is NOT in the transaction.
ba_forvaltning is the name of the database you are importing into
ba_import is the name of the source database (I just restored over this when I was done with one database)
-- Use the database you want to import from
use ba_import
begin tran
insert into ba_forvaltning..r_status (name, description, statusrank, completedTask, commentrequired, negative, closedtask)
select name, description, statusrank, completedTask, commentrequired, negative, 0
from r_status where name not in (select name collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..r_status)
insert into ba_forvaltning..r_urgency (name, description, colour, urgencyrank)
select name, description, colour, urgencyid from r_urgency where name not in (select name collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..r_urgency)
insert into ba_forvaltning..r_tasktype (name, description)
select name, description from r_tasktype
where name not in (select name collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..r_tasktype)
insert into ba_forvaltning..owner (firstname, lastname, email, phone, roleid, password, alerts, teamalerts, dailyalerts)
select firstname, lastname, email, phone, roleid, password, alerts, teamalerts, dailyalerts
from owner where email not in (select email collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..owner)
insert into ba_forvaltning..system (name, ownerid, kb, [default],tc)
select name, ownerid, kb, 0, 0
from system where name not in (select name collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..system)
insert into ba_forvaltning..task (
typeid, name, description, statusid, urgencyid, taskts, toownerid, fromownerid, systemid,
componentid, url, version, closedts )
select
(select typeid from ba_forvaltning..r_tasktype where name collate SQL_Latin1_General_CP1_CI_AS in
(select name from r_tasktype where typeid = task.typeid))
, name, description,
(select statusid from ba_forvaltning..r_status where name collate SQL_Latin1_General_CP1_CI_AS in
(select name from r_status where statusid = task.statusid)),
(select urgencyid from ba_forvaltning..r_urgency where name collate SQL_Latin1_General_CP1_CI_AS in
(select name from r_urgency where urgencyid = task.urgencyid)),
taskts,
(select ownerid from ba_forvaltning..owner where email collate SQL_Latin1_General_CP1_CI_AS in
(select email from owner where ownerid = task.toownerid)
),
(select ownerid from ba_forvaltning..owner where email collate SQL_Latin1_General_CP1_CI_AS in
(select email from owner where ownerid = task.fromownerid)
),
(select systemid from ba_forvaltning..system where name collate SQL_Latin1_General_CP1_CI_AS in
(select name from system where systemid = task.systemid)
),
(select componentid from ba_forvaltning..component where name collate SQL_Latin1_General_CP1_CI_AS in
(select name from component where componentid = task.componentid)
) as componentid
, url, version, closedts
from task
where name not in (select name collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..task)
print 'stakeholder'
select
(select top 1 taskid from ba_forvaltning..task where cast(description as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS in
(select cast(description as varchar(8000)) from task where taskid = stakeholder.taskid) and
cast(name as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS in
(select cast(name as varchar(8000)) from task where taskid = stakeholder.taskid)
) taskid,
(select ownerid from ba_forvaltning..owner where email collate SQL_Latin1_General_CP1_CI_AS in
(select email from owner where ownerid = stakeholder.ownerid)
) ownerid
into #tmp
from stakeholder
insert into ba_forvaltning..stakeholder (taskid, ownerid)
select * from #tmp a
where not exists (select * from
ba_forvaltning..stakeholder b
where a.taskid = b.taskid
and a.ownerid = b.ownerid
)
insert into ba_forvaltning..thread (taskid, messagets, message, ownername)
select
(select top 1 taskid from ba_forvaltning..task where cast(description as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS in
(select cast(description as varchar(8000)) from task where taskid = thread.taskid) and
cast(name as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS in
(select cast(name as varchar(8000)) from task where taskid = thread.taskid)
) taskid,
messagets, message,
(select firstname + ' ' + lastname from ba_forvaltning..owner where email collate SQL_Latin1_General_CP1_CI_AS in
(select email from owner where ownerid = thread.ownerid)
) ownername
from thread where cast ( message as varchar(8000)) not in
(select cast(message as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..thread)
/*
--TODO: change the paths
insert into ba_forvaltning..taskfile (taskid, filename, path)
select
(select top 1 taskid from ba_forvaltning..task where cast(description as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS in
(select cast(description as varchar(8000)) from task where taskid = taskfile.taskid) and
cast(name as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS in
(select cast(name as varchar(8000)) from task where taskid = taskfile.taskid)
) taskid,
filename,
replace(path,'/tarantell/support/bugaware/','/forvaltning/')
from taskfile where cast ( filename as varchar(8000)) not in
(select cast(filename as varchar(8000)) collate SQL_Latin1_General_CP1_CI_AS from ba_forvaltning..taskfile)
declare @cmd varchar(8000)
--TODO: change the paths
declare tmp cursor for
select 'move c:\Wwwroot\forvaltning\data\old\' + replace(path,'/forvaltning/data/','')
+ ' c:\Wwwroot\forvaltning\data\' + cast(taskid as varchar(50))
from ba_forvaltning..taskfile
open tmp
while 0 = 0
begin
fetch next from tmp into @cmd
if @@fetch_status <> 0 BREAK
exec master..xp_cmdshell @cmd
end
close tmp
deallocate tmp
*/
drop table #tmp
rollback tran
|
|
|
|
|
Report
|
|
|
|