Copy parent-child records with different Ids.

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

General SQL Server Forums

New to SQL Server Programming

Copy parent-child records with different Ids.

Author

Topic

oleanganesh
Starting Member

3 Posts

Posted - 2013-12-11 : 14:53:57

Hi

I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables? Any help appreciated/

Thanks Venky

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-11 : 14:57:57

you mean this?

insert parts (partid, parentpartid)
select newid(), parentpartid from parts

Be One with the Optimizer
TG

oleanganesh
Starting Member

3 Posts

Posted - 2013-12-12 : 12:08:46

No, I should also have the corresponding changed new parentpartid for the partid.
eg:
old partid: 66666666666666666666, old parentpartid: null
old part id: 5555555555555555555, old parentpartid: 66666666666666666666

new partid : 88888888888888888888, new parentpartid: null
new partid : 7777777777777777777, new parentpartid: 88888888888888888888

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 13:29:11

I see. Then this:


select partid, newPartid = newid() into #partMap from parts group by partid

insert parts (partid, parentpartid)
select t.newpartid, tp.newpartid
from   parts p
inner join #partMap pm on pm.partid = p.partid
left outer join #partMap pmp on pmp.partid = p.parentpartid

Be One with the Optimizer
TG

oleanganesh
Starting Member

3 Posts

Posted - 2013-12-12 : 15:22:37

Thanks TG. That worked. I have to change t and tp in select to pm and pmp.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 15:25:15

Ah - sorry about that I missed changing that when I renamed my aliases.
Glad it worked.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources