How can I merge two SQL tables and then append the result to a third?

user_14859906 0
Viewed 48

Say I have the following three tables:

Table 1

ID NAME
 1 John

Table 2

ID   ITEM
 1  apple
 2 orange
 3 banana

Table 3

ID NAME   ITEM
 1 Mike  mango
 2 Mike  grape

What is the most efficient way using SQL to merge Tables 1 and 2 together and append the result to Table 3 in order to get the following result (but leave 1 and 2 as they are)?

Table 3 after operation

ID NAME   ITEM
  1 Mike  mango
  2 Mike  grape
  3 John  apple
  4 John orange
  5 John banana
postgresql sql
2 Answers

You can use cross join with union all:

select row_number() over (order by t.name, t.item) id, t.* from (
    select t2.name, t2.item from table2 t2
    union all
    select t1.name, t3.item from table1 t1 cross join table3 t3) t
user_73267380

Just insert a cross join between the 2.
But don't insert those that already exist in table 3.

insert into "Table 3" (name, item) 
select t1.name, t2.item
from "Table 1" as t1
cross join "Table 2" as t2
where not exists (
  select 1
  from "Table 3" t3
  where t3.name = t1.name
    and t3.item = t2.item
);
user_40034190
Related