Copy entire parent/child relationship

I have a table containing rows with parent/child relationships, like this:


id title        parent_id
1  Home         NULL
2  Products     NULL
3  Scooter      2
4  Tricycle     2
5  About us     NULL
6  Services     NULL
12 Quad bike    2
20 FAQs         5

I want to make a copy of this data and preserve the relationships in the copy (so that the duplicated Quad bike page’s parent_id would reflect the new id assigned to the duplicated Products page). I’m using SQL Server 2005. Is there a quick way of doing this? There are about 2500 rows of data which I’d rather not have to sort through manually :slight_smile:

Any input would be much appreciated.

Well I managed to get it working using the example query given in the above article, so consider this problem solved :slight_smile:

i did not investigate that article in any detail but if you have trouble adapting it to your table, post your attempt here and we’ll work it out

Sorry to be unclear. The data needs to be copied into the same table. Perhaps I could have been more sensible and put an important additional column in my example table structure — country_code — which is the only field that will change between the original data and the duplicated data.

The need for replication is because we’re introducing a US version of one of our sites and the content for UK/US versions will be exactly the same to start with.

I did actually do a bit more Googling since posting my question and I did find this thread, which I’ll try out tomorrow morning at work.

where?

i mean, in the same table, or in the same database, or where?