Merge temp table rows into table for each distinct ID

Question

I have an output of markers (currently output to a temp table) that I need to merge into another table. Each set of markers should be inserted as a new row for each distinct ID in the final table.

Example:
Temp Table:

Marker

------

ABC
ABB
ACC
etc (There are about 50,000 rows in this temp table.

ID Table
ID        Marker
---       ------

1         Existing Marker
1         ABC
1         ABB
1         ACC
1         etc (for all 50k markers)
2         Existing Marker
2         ABC
2         ABB
2         ACC
2         etc (for all 50k markers)
Repeat for all ID's in the table. Anywhere from 150-5000.

 

 

Answer 1

Across joinshould do it, it would produce the Cartesian product of the set of markers in the temp table and the set of ids in the id table:

insert ID_Table (id, marker)
select distinct i.id, m.marker
from ID_Table i
cross join temp_table m
-- or use the statement below to filter out existing markers
-- cross join (
--   select marker 
--   from temp_table 
--   where marker not in (
--     select marker from id_table
--   )
-- ) m

This assumes that the new markers from the temp table aren't already in the id table - if they might be you'll want to filter out the existing ones first.

Although beware that combining 50'000 rows with 150-5'000 will create a lot of rows (between 7.5 and 250 millions) and potentially take a very long time; but maybe this is what you want?