Insert copy of a row multiple times with 1 column changed

Question

I have the table

+---------------------+
| ID | Code | Amount  |
+---------------------+
| 1  | 101  | 1.2     | 
| 2  | 101  | 1.3     |
| 3  | 101  | 1.3     |
| 4  | 101  | 1.4     |
| 5  | 101  | 1.2     |
| 6  | 101  | 1.5     |
| 7  | 101  | 1.4     |
| 8  | 101  | 1.3     |
| 9  | 101  | 1.1     |
| 10 | 101  | 1.0     |
+---------------------+

I am trying to copy each row with a unique ID around 20 times and changing

So my output should be like this.

+---------------------+
| ID | Code | Amount -|
+---------------------+
| 1  | 101  | 1.2     |
| 1  | 102  | 1.2     | 
| 1  | 103  | 1.2     | 
| 1  | 104  | 1.2     |  
| 1  | 105  | 1.2     |
| 1  | 106  | 1.2     |
| 1  | 107  | 1.2     |
| 1  | 108  | 1.2     |
| 1  | 109  | 1.2     |
| 1  | 110  | 1.2     |
| 1  | 115  | 1.2     |
| 1  | 118  | 1.2     |
| 1  | 121  | 1.2     |
| 1  | 119  | 1.2     | 
| 2  | 101  | 1.3     | 
| 2  | 102  | 1.3     | 
| 2  | 103  | 1.3     | 
| 2  | 104  | 1.3     | 
+---------------------+

And so on for the table. The codes are going to be the same for each ID. They are not in an order like my example table above so I will need to hard code them in somewhere. There is 20 more codes I need to add for each ID. The amount will be the same. The ID will be the same. But the code is always going to be different.

I need to update this table to reflect this.

I am having trouble doing it. I can copy the row once. And then change the code. But I can only do that for the first ID and I cannot do any more. Help is very much apreciated.

Thanks,

"I am having trouble doing it." - any errors?

Yuri
– Yuri

2015-06-09 14:23:39 +00:00
Commented Jun 9, 2015 at 14:23 — Yuri
– Yuri, Commented Jun 9, 2015 at 14:23

Ruud Helderman · Accepted Answer · 2015-06-09 14:24:46Z

1

Create a simple 1-column table with the additional codes.

CREATE TABLE AdditionalCodes (Code int)

INSERT INTO AdditionalCodes VALUES (102), (103), (104), (105)

Now all you have to do is join this table with the original table, and insert the result.

INSERT INTO MyTable (ID, Code, Amount)
SELECT t.ID, c.Code, t.Amount
FROM MyTable t, AdditionalCodes c

answered Jun 9, 2015 at 14:24

Ruud Helderman

11.1k1 gold badge28 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

Thank you this works perfectly. I already have the codes I need in a table for something else. Just couldn't get my head right today to figure this part out.

Gordon Linoff · Accepted Answer · 2015-06-09 14:27:46Z

1

You can use a CTE or subquery to define the codes. Here is a method using VALUES():

select t.id, c.code, t.amount
from thetable t cross join
     (values(101), (102), (103), . . . ) codes(code);

answered Jun 9, 2015 at 14:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Insert copy of a row multiple times with 1 column changed

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related