duplicate rows


I have a table that creates a tree. It is like this:
Id(int), ParentId(int)
101, 0
105, 101
190, 101
195, 190
I want to duplicate the items so the result should be something like this:
201, 0
205, 201
290, 201
295, 290

How is best to do this'
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
The only thing that's coming to mind is to use a variable to get the first id inserted, then use an UPDATE to cheat your way through the rest...

<air code.  I do my own stunts too>
Declare @min_id int, @max_id int

-- Get the current min and max ID
SELECT @min_id = Min(id), @max_id = Max(id) 
FROM MyTable

-- Insert the duplicate rows
INSERT INTO MyTable(ParentID) 
FROM MyTable

-- New rewrite the ParentID's
SET ParentID = ParentID + (@max_id - @min_id) 
WHERE id > @max_id

Open in new window

Another possibility would be to use the OUTPUT clause to get the newly-created id's in the INSERT, then use that for future UPDATE of the ParentId.

btw I have an article out there called SQL Server Delete Duplicate Rows Solutions, but this is the first question I've come across where the requirement is to add + manipulate duplicate rows.
Mike EghtebasDatabase and Application DeveloperCommented:
In Query:
SELECT Id + 100 As id, ParentId
From Table1

Open in new window

As a new Table:
Create Table Table2(Id(int), ParentId(int));
Insert Table2(Id, ParentId) 
Select Id + 100 As id, ParentId From Table1;  

Open in new window

In the same table:
Update Table1 Set id = id +100;

Open in new window

Provided id is uneatable.
johnson1Author Commented:
The Id is identity column, so I do not know the id number until after I have inserted the row.
Mike EghtebasDatabase and Application DeveloperCommented:

Do you want:

a. In Query?
b. As a new Table? or
c. In the same table?

(see my first post)

It is not clear what you are asking for. Could you please explain a bit?

johnson1Author Commented:
I am doing this in a query. I already have a table with thousands of rows. I want to duplicate the rows, but each new row will get new Id because Id is an identity column. And the ParentId has to point to the new id.

 In the real table I have also a column that groups toghether the rows. I added below the column GroupId.

Id(int), ParentId(int), GroupId(int)
101, 0, 10
105, 101, 10
190, 101, 10
195, 190, 10
121, 0, 11
125, 121, 11
127, 121, 11
139, 127, 11

I would like to copy the rows that have certain GroupId. For example GroupId=10.
I want to insert the rows into the same table and create a new groupId.

Please let me know if this is not clear.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.