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

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

CodePudding user response：

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

CodePudding user response：

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
);