CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Fred Rogers
Fred Rogers

Reputation: 423

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

I need to merge Table1 and Table2 into 1 master table. I know this can be done by something like the below query -

   INSERT INTO masterTable(Id, CountryFk, CompanyName)
   SELECT * FROM Table1
   UNION
   SELECT * FROM Table2;

But, I have an extra column, website in table2 which isn't there in table1. I need this column in masterTable.

And more importantly, Table1 and Table2 have repeating companies with the same countryFK. For eg, IBM at countryFK=123 could be present twice in Table1. And Table1 could have a companyName that is present in Table2.

For eg: IBM at countryFk = 123 could be present in table1 and table2. I need to make sure that the masterTable does not have any duplicate companies.

Please note that the companyname by itself need not be unique. masterTable can have IBM with countryFK = 123 and IBM with countryFk = 321.

masterTable cannot have IBM with countryFk=123 twice.

Upvotes: 0

Views: 568

Answers (4)

paparazzo
paparazzo

Reputation: 45096

INSERT INTO masterTable(CountryFk, CompanyName, WebSite)
SELECT CountryFk, CompanyName, min(WebSite) 
  FROM Table2 
 group by CountryFk, CompanyName;
INSERT INTO masterTable(CountryFk, CompanyName)
SELECT distinct CountryFk, CompanyName
  FROM Table1 
  LEFT JOIN masterTable
    on masterTable.CountryFk = Table1.CountryFk 
   and masterTable.CompanyName = Table1.CompanyName 
 where masterTable.CountryFk is null;

Upvotes: 0

Eric
Eric

Reputation: 5743

IMHO, if you need to ensure both CompanyName and CountryFk not duplicate in MasterTable, you should add an unique index on the column.

Below query selects all distinct value in Table1 and Table2, and insert with existence checking for both CompanyName and CountryFk.

-- Id is identity, no need to insert value
INSERT MasterTable (CountryFk, CompanyName, WebSite)
SELECT 
    CountryFk, 
    CompanyName,
    (
       SELECT TOP(1) WebSite FROM Table2 
       WHERE CompanyName = data.CompanyName  
           AND CountryFk = data.CountryFk 
           AND WebSite IS NOT NULL
    ) AS WebSite
FROM 
(
    SELECT CountryFk, CompanyName FROM Table1
    UNION
    SELECT CountryFk, CompanyName FROM Table2
) data
WHERE 
    NOT EXISTS
    (
        SELECT * FROM MasterTable 
        WHERE CompanyName = data.CompanyName AND CountryFk = data.CountryFk
    )
GROUP BY
    CountryFk, 
    CompanyName

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

This may work

INSERT INTO masterTable(Id, CountryFk, CompanyName,Website)
   SELECT Id, CountryFk, CompanyName, NULL as Website FROM Table1
   UNION
   SELECT Id, CountryFk, CompanyName,Website FROM Table2;

Upvotes: 1

Ravi
Ravi

Reputation: 31417

Try this for proper UNION.

INSERT INTO masterTable(Id, CountryFk, CompanyName,Website)
   SELECT Id, CountryFk, CompanyName, "Website" as Website FROM Table1
   UNION
   SELECT Id, CountryFk, CompanyName, Website FROM Table2 WHERE CompanyName NOT IN(SELECT CompanyName FROM Table1);

Or

Once, you have merge your both table data into master table, then you can look for duplicate data and remove them 

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY CountryFk, CompanyName 
                                       ORDER BY ( SELECT 0)) RN
         FROM   MyTable)
DELETE FROM cte
WHERE  RN > 1

Upvotes: 0

Related Questions