CODEWITHSUNDEEP
sqlsql-server
Sam
Sam

Reputation: 948

Insert copy of a row multiple times with 1 column changed

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,

Upvotes: 1

Views: 72

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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);

Upvotes: 1

Ruud Helderman
Ruud Helderman

Reputation: 11028

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

Upvotes: 1

Related Questions