October 12, 2007 at 2:50 am
hi,
Actually, I've planned to move the data from source db to another db. In the source db, nearly 500 tables are there. Each and every tables having a relationship and I dont know how to prioritize the table for generating the insert script for moving the data from source db to destination db.
October 12, 2007 at 3:05 am
Backup source database and restore it on destination server using with move option.
October 12, 2007 at 3:13 am
Hi,
Thanks for your reply. I think, u cant understand my situation. My requirement is, I've taken the back up and also moved the data to the destination. That's not a problem. I want to schedule the package for that the data which is older than 2 years should be moved from source db to destination db. And in the package, i need to design the SP fro insert query script which is used for moving the data from source db to destination db. In that insert query table script, I've included 150 tables.But, in that 150 tables , each and every one is referenced and How to prioritize the tables list and how to create the insert script.
October 12, 2007 at 3:23 am
One way of doing it, if you have the patience, is to use the database diagram tool in Enterprise Manager. This will show you which tables have relationships with which others, and enable you to decide which are the parent tables that you need to move first.
John
October 12, 2007 at 3:33 am
Hi,
Thanks for your reply. Actually, in the past, I've followed two ways.
1. In the destination db, I switch off all the foreign key constraints and then try to move the data. But, according to this way, I dont know whether the data flow may correct or not. So, I dropped this way.
2. As per your suggestion, I used this way. But, nearly more than 150 tables are there. And, Using DB diagram, I cant able to analyze and also it takes more time for me for prioritize.
Can you please tell me that is there is any other way?
October 12, 2007 at 3:44 am
There is another way, and that's to use the information in sysreferences and sysconstraints (I think) to write a script that will give you the tables in a hierarchical order. I tried this once but never quite got it to work. Still, I imagine somebody has done, so you could try searching this site and others.
Good luck!
John
October 12, 2007 at 3:57 am
Thanks john.
This is my first forum in this site. Really super and it is very useful to my career. Great SQLServerCentral.com....Cheers:)
October 12, 2007 at 4:30 am
Long time ago I created this script in order to delete all records from current database and I think it can be start point for what you need.
declare @Order int
declare @id int
DECLARE @strCmd nvarchar(1000)
set nocount on
Set @Order = 1
create table #a ([id] sysname, [order] int)
create table #b (fid sysname, rid sysname)
-- First, take tables which are not referenced by any foreign key
insert into #a
select id, 0
from sysobjects left join sysforeignkeys on id = rkeyid
where objectproperty(id, 'isusertable') = 1
and rkeyid is null
-- Make a clone of key pairs from sysforeignkeys
insert into #b
select distinct fkeyid, rkeyid
from sysforeignkeys
-- While there are leaves tables do:
while exists(select distinct f1.fid
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null)
begin
-- Insert leaves first
insert into #a
select distinct f1.fid, @Order
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
-- Delete just "ordered" tables
delete f1
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
Set @Order = @Order + 1
end
-- Insert if something's rest in #b (e.g. self-join)
insert into #a
select distinct fid, @Order
from #b
declare c cursor
for
select id, [order]
from #a
order by [order] desc --asc if you need reverse order
open c
--begin tran
FETCH NEXT FROM c INTO @id, @Order
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- Put your complete command hear
SET @strCmd = N'INSERT INTO ' + quotename(Object_name(@id))
--EXEC sp_executesql @strCmd
PRINT @strCmd
--PRINT cast(quotename(Object_name(@id)) as char(50)) + cast(@@rowcount as char(7)) + ' row(s) inserted.'
END
FETCH NEXT FROM c INTO @id, @Order
END
CLOSE c
DEALLOCATE c
--rollback
drop table #a
drop table #b
set nocount on
October 31, 2007 at 7:12 am
Hi Nebojsa,
Thanks a lot for your response. Sorry for taking a delay.
October 31, 2007 at 8:45 am
Hi,
I've an doubt regarding your script. I executed this script, but in the output contains the same table name twice or thrice. Can you explain abt this script if possible. This script generate the script from the child table and what happened abt the parent table. I want the script up to parent table. Can you clear my doubt please.
October 31, 2007 at 8:57 am
Hi,
I'm not sure that I understand well. I've tested this script over a lot of different databases. But it's possible to have some bug. Can you e-mail me script for your db (tables, primary keys and foreign keys at least)?
October 31, 2007 at 9:26 am
Sorry, I found it. I'll try to fix it tomorrow.
October 31, 2007 at 10:43 pm
Hi,
Thanks a lot for your reply. I am waiting for your reply. Can you please fix the script as soon as possible.
November 1, 2007 at 1:05 am
I'll be back with fix very soon.
November 1, 2007 at 1:56 am
I made corection and tried to test it over many different structures. Pls, give me a feedback.
set nocount on
declare @Order int
Set @Order = 1
IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#a'))
DROP TABLE #a
IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#b'))
DROP TABLE #b
create table #a ([id] int, [order] int)
create table #b (fid int, rid int)
-- First, delete from tables which are not referenced by any foreign key
insert into #a
select id, 0
from sysobjects left join sysforeignkeys on id = rkeyid
where objectproperty(id, 'isusertable') = 1
and rkeyid is null
-- Make a clone of key pairs from sysforeignkeys
insert into #b
select distinct fkeyid, rkeyid
from sysforeignkeys
-- While there are leaves tables do:
while exists(select distinct f1.fid
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null)
begin
-- Insert leaves first
insert into #a
select distinct f1.fid, @Order
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
-- Delete just "ordered" tables
delete f1
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
Set @Order = @Order + 1
end
-- Insert if something is rest in #b (e.g. self-join)
insert into #a
select distinct fid, @Order
from #b
-- Insert top level tables
insert into #a
select distinct f1.rkeyid, @Order + 1
from sysforeignkeys f1 left join sysforeignkeys f2 on f1.rkeyid = f2.fkeyid
where f2.rkeyid is null
declare @id int
DECLARE @strCmd nvarchar(1000)
-- Now when we have all tables in temporary table #a
-- we can create dynamic script and execute it
-- If you need list like this often you can create table-valued function
-- starting code from this line, replacing temp table with function call
declare c cursor
for
select [id], max([order])
from #a
group by [id]
order by max([order]) desc --asc if you need reverse order (for delete)
open c
--begin tran
FETCH NEXT FROM c INTO @id, @Order
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @strCmd = N'INSERT INTO ' + quotename(Object_name(@id))
-- EXEC sp_executesql @strCmd
PRINT cast(quotename(Object_name(@id)) as char(50)) + cast(@@rowcount as char(7)) + ' row(s) deleted '
END
FETCH NEXT FROM c INTO @id, @Order
END
CLOSE c
DEALLOCATE c
--commit
--rollback
drop table #a
drop table #b
set nocount off
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply