Copy entire parent/child relationship

[SJH]

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

Any input would be much appreciated.

[r937]

I want to make a copy of this data and preserve the relationships in the copy

where?

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

[SJH]

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.

[r937]

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

[SJH]

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