how can I join two very different table structures? No common keys

For the purpose of good database design, I’ve never run into an issue like this, but we are currently changing systems and there is a new key system that will need to be merged in with the old. I can create a surrogate key, but it’ll be a lot of effort to prevent duplicate keys at that point. A number of the fields will line up, but no records will match on their keys since the two systems will not overlap.

Lets say for example we have something like this….

Old Tables

ID | Field1 | Field2 | Field3
1  | asdfgh | asdfgh | asdfgh
2  | asdfgh | asdfgh | asdfgh
3  | asdfgh | asdfgh | asdfgh

New Table (with composite keys)

ID1 | ID2 | Field1 | Field3 | Field4
1   | 1   | asdfgh | asdfgh | asdfgh
2   | 2   | asdfgh | asdfgh | asdfgh
3   | 3   | asdfgh | asdfgh | asdfgh

I’d like an end result like this:

ID | ID1 | ID2 | Field1 | Field2 | Field3 | Field4
1  |     |     | asdfgh | asdfgh | asdfgh | 
2  |     |     | asdfgh | asdfgh | asdfgh | 
3  |     |     | asdfgh | asdfgh | asdfgh | 
   | 1   | 1   | asdfgh |        | asdfgh | asdfgh
   | 2   | 2   | asdfgh |        | asdfgh | asdfgh
   | 3   | 3   | asdfgh |        | asdfgh | asdfgh

This would be an ugly table, but it’ll let me turn it into a composite across the three ID fields. How could I create this table from the two previous ones? Also, the fields can line up in any order.

You are looking for UNION ALL

select id,null as id1,null as id2,field1,field2,field3,null as field4
from old_table
union all
select null as id, id1, id2,field1,null as field2,field3,field4
from new_table;

Result:

id    id1 id2 field1  field2  field3  field4
1         asdfgh  asdfgh  asdfgh  
2         asdfgh  asdfgh  asdfgh  
3         asdfgh  asdfgh  asdfgh  
     1    1   asdfgh          asdfgh  asdfgh
     2    2   asdfgh          asdfgh  asdfgh
     3    3   asdfgh          asdfgh  asdfgh

Demo