How copy data in the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How copy data in the same table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-06 : 09:34:57

Suvi writes "Hi!

I have problem with copying data in SQL server 2000

I have row which have multiple subrows.
4 tables and all have different number subrows.

Here you can see the chain and ID's

 
Order_ID (pk)
	Product_ID (pk), Order_ID
	Product_ID (pk), Order_id
		Component_ID (pk), Product_ID
		Component_ID (pk), Product_ID
	 		Part_ID (pk), Component_ID
			Part_ID (pk), Component_id

I wan't to copy this chain in same table.
All Primary keys are identity types and inserted automaticly.
Copied rows get new ID's and related sub rows should get new ID's.

I actaually need stored prosedure which copy project, call next prosedure,
which copy related Products and that call next prosedure which copy Component ....

Any good ideas?

Suvi"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-07 : 11:35:11

Something like (haven't compiled this)

create table #a (new_id int, old_id int, new_link int, old_link int, seq int, id int identity (1,1))
insert #a select 0, @Order_id, 0, 1
insert #a select 0, Product_ID, 0, @Order_ID, 0, 2 from tbl2 where Order_ID = @Order_ID
insert #a select 0, Component_ID, 0, Product_ID, 3 from tbl3
where Order_id in (select old_id from #a where seq = 2)
insert #a select 0, Part_ID, 0, Component_ID, 4 from tbl4
where Component_ID in (select old_id from #a where seq = 3)

declare @seq int
set @seq = 1
declare @id int
select @id = max(Order_ID) from tbl1
insert tbl1 select ... from #a where seq = @seq -- new order ID
select @id = min(Order_ID) from tbl1 where Order_ID > @id
update #a set new_id = @id + id - (select min(id) from #a where seq = @seq)
where seq = @seq

set @seq = @seq + 1
update #a set new_link = new_id
from #a a1, #a a2
where a2.seq = @seq - 1
and a1.seq = 1
and a1.old_link = a2.old_id

select @id = max(Order_ID) from tbl2
insert tbl2
select select new_link from #a where seq = @seq order by id
select @id = min(Component_ID) from tbl2 where Component_ID > @id
update #a set new_id = @id + id - (select min(id) from #a where seq = @seq)
where seq = @seq

ditto for tbl3

Note this is a bit dangerous as you can't really guarantee the order of the inserted records - but usually can.
It is better if you have some other field which is unique (like a description) which you can use to obtain the ID.
Otherwise you can insert one by one and use the same process.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

suvi
Starting Member

1 Post

Posted - 2002-08-08 : 09:06:17

I try to do something like this. This isn't working right.
The first tree level works fine, but last (level-four) doesn't.
All part's get same @new_component_key

 
CREATE PROCEDURE [dbo].[KOPIOI_PROJEKTI_LAITE]
@vanha_projekti_key int

AS
set nocount on 

declare @new_project_key int
declare @old_product_key int
declare @new_product_key int
declare @old_component_key int
declare @new_component_key int

	insert into project (name, type)
	select name, type
	from  project
	where project_key =@old_product_key

	select @new_project_key= @@identity

declare product_cursor CURSOR FOR
	select product_key from product where project_key = @old_product_key
open product_cursor
fetch next from product_cursor into @old_product_key
while @@fetch_status = 0
begin
	insert into product (positio, project_key)
	select positio, @new_project_key from product where product_key = @old_product_key
	select @new_product_key = @@identity
		insert into component (positio, product_key)
		select positio, @new_product_key from component where product_key = @old_product_key
		select @new_component_key= @@identity
		  declare component_curosr CURSOR FOR
			select component_key from component where product_key = @old_product_key
			open component_curosr	
			fetch next from component_cursor into @old_component_key
			while @@fetch_status = 0
			begin	
					insert into part (positio, component_key)
					select positio, @new_component_key from  part where component_key = @old_component_key
					fetch next from component_cursor into @old_component_key
			end
			close component_curosr
			deallocate component_curosr
					
	fetch next from product_cursor into @old_product_key
		
end
close  product_cursor
deallocate product_cursor

This help me guite much
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=16836

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources