Inserting data from two similar tables into one master table in Sql Server -

• table1 -> id, countryfk, companyname
• table2 -> id, countryfk, companyname, website

i need merge table1 , table2 1 master table. know can done below query -

   insert mastertable(id, countryfk, companyname)    select * table1    union    select * table2;  

but, have column, website in table2 isn't there in table1. need column in mastertable.

and more importantly, table1 , table2 have repeating companies same countryfk. eg, ibm @ countryfk=123 present twice in table1. , table1 have companyname present in table2.

for eg: ibm @ countryfk = 123 present in table1 , table2. need make sure mastertable not have duplicate companies.

please note companyname need not unique. mastertable can have ibm countryfk = 123 , ibm countryfk = 321.

mastertable cannot have ibm countryfk=123 twice.

imho, if need ensure both companyname , countryfk not duplicate in mastertable, should add unique index on column.

below query selects distinct value in table1 , table2, , insert existence checking both companyname , countryfk.

-- id identity, no need insert value insert mastertable (countryfk, companyname, website) select      countryfk,      companyname,     (        select top(1) website table2         companyname = data.companyname              , countryfk = data.countryfk             , website not null     ) website  (     select countryfk, companyname table1     union     select countryfk, companyname table2 ) data      not exists     (         select * mastertable          companyname = data.companyname , countryfk = data.countryfk     ) group     countryfk,      companyname