CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Stad
Stad

Reputation: 236

How to insert multiple rows into one table for each id of another table

I have 2 Tables (20000 +rows) :

Table1:

C1_ID |C1_Name | L1_ID | C2_ID 
------------------------------------
a     | Alan   |123    |  k     
b     | Ben    |345    |  l     
a     | Alan   |123    |  m     
a     | Alan   |453    |  n     
c     | John   |111    |  i     
f     | Sasha  |987    |  e
c     | John   |111    |  s    
c     | John   |756    |null
z     | Peter  |145    |null

Table2:

C2_ID |L2_ID|Category
---------------------
k     |888 |1
k     |789 |2
k     |888 |1
l     |456 |0
l     |147 |1
m     |333 |1
n     |999 |2
n     |369 |4
n     |258 |3
i     |159 |2
i     |357 |1
e     |684 |1
s     |153 |2

Desired output:

C1_ID |C1_Name | L1_ID | C2_ID| L2_ID|Category
----------------------------------------------
a     | Alan   |123    |  k   |888   |1
a     | Alan   |123    |  k   |789   |2  
a     | Alan   |123    |  m   |333   |1 
a     | Alan   |453    |  n   |999   |2 
a     | Alan   |453    |  n   |369   |4 
a     | Alan   |453    |  n   |258   |3 
b     | Ben    |345    |  l   |456   |0
b     | Ben    |345    |  l   |147   |1  
c     | John   |111    |  i   |159   |2  
c     | John   |111    |  i   |357   |1
c     | John   |111    |  s   |153   |2
c     | John   |756    |null  |null  |null
f     | Sasha  |987    |  e   |684   |1 
z     | Peter  |145    |null  |null  |null

I need to update table 1 and add rows for each time C2_ID(with distinct L2_ID) is found in table 2. The row should be updated with the relevant L2_Id and Category.

Upvotes: 0

Views: 334

Answers (1)

Hogan
Hogan

Reputation: 70538

This is a simple join.

SELECT * 
FROM TABLE1 T1
LEFT JOIN 
 (SELECT DISTNCT C2_ID, L2_ID, Category
  FROM TABLE2) AS T2 ON T1.C2_ID = T2.C2_ID

Upvotes: 1

Related Questions