CODEWITHSUNDEEP
sqlsql-serversql-insert
pancufer
pancufer

Reputation: 17

Inserting set of rows for every ID in another table

this is an initial table (this is just a part of a larger table where Article ID's can vary), database is MS Sql.

-----------------------------------
|ArticleID |     GroupID          |
-----------------------------------
|      1   |        NULL          |
-----------------------------------
|      2   |        NULL          | 
-----------------------------------
|      3   |        NULL          |
-----------------------------------
|      4   |        NULL          | 
-----------------------------------

Set of rows that should be entered for each ArticleID looks something like this:

------------------------
|      GroupID         |
------------------------
|         A            |
------------------------
|         B            | 
------------------------
|         C            |
------------------------
|         D            | 
------------------------

Result table should look something like this:

-----------------------------------
|ArticleID |     GroupID          |
-----------------------------------
|      1   |        NULL          |
-----------------------------------
|      1   |         A            | 
-----------------------------------
|      1   |         B            |
-----------------------------------
|      1   |         C            | 
-----------------------------------
|      1   |         D            | 
-----------------------------------
|      2   |        NULL          |
-----------------------------------
|      2   |         A            | 
-----------------------------------
|      2   |         B            |
-----------------------------------
|      2   |         C            | 
-----------------------------------
|      2   |         D            | 
-----------------------------------
|      3   |        NULL          |
-----------------------------------
|      3   |         A            | 
-----------------------------------
|      3   |         B            |
-----------------------------------
|      3   |         C            | 
-----------------------------------
|      3   |         D            | 
-----------------------------------
|      4   |        NULL          |
-----------------------------------
|      4   |         A            | 
-----------------------------------
|      4   |         B            |
-----------------------------------
|      4   |         C            | 
-----------------------------------
|      4   |         D            | 
-----------------------------------

Any suggestion how to insert it efficiently? Thanks a lot for you suggestion.

Regards

Upvotes: 0

Views: 451

Answers (1)

Stu
Stu

Reputation: 32579

This is a cross join between two sets.

with a as (
  select * from(values (1),(2),(3),(4))v(ArticleId)
), g as (
 select * from(values (null),('A'),('B'),('C'),('D'))v(GroupId)
)
select *
from a cross join g;

To insert into the original table you could do:

with g as (select * from(values('A'),('B'),('C'),('D'))v(GroupId))
insert into t
select t.ArticleId, g.GroupId
from t cross join g;

See Example Fiddle

Upvotes: 1

Related Questions