Solved

duplicate rows

Posted on 2015-02-16
5
54 Views
Last Modified: 2015-02-19
Hello,

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'
0
Comment
Question by:johnson1
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40612914
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));
GO
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.
0
 

Author Comment

by:johnson1
ID: 40612973
The Id is identity column, so I do not know the id number until after I have inserted the row.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40613373
johnson1,

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?

Mike
0
 

Author Comment

by:johnson1
ID: 40613760
Hello,
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.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40615206
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) 
SELECT ParentId
FROM MyTable

-- New rewrite the ParentID's
UPDATE MyTable
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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Set Field Values ito Zero Based on Related Table 4 40
SSAS Hierarchy with columns with folder names 10 19
SQL DATEADD 10 70
Are triggers slow? 7 12
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question