help needed on combining two big tables

General
Enthusiast

help needed on combining two big tables

Hi ,

I need your quick help on combining two big tables, each table has data in the range of Billion/Millions.

 

My Question:  I have two tables namely Table1 & Table2. Let us take the below sample data

 

Table1:

ID             Name             Sal

10            A                 1000

20            A                  2000

30            A                   3000

 

Table2:

 

ID             Name           Sal

10            B                  10000

20            B                   20000

40            B                  40000

 

I need to combine these two tables and load this data into final table.

If ID present is in both the tables then pick only Table1 ID value. basically we should load complete Table1 data into final table and when it comes to table2 , process only the ID's which are not present in table 1.

 

My output should look like:

 

ID           NAME     SAL

10            A                 1000

20            A                  2000

30            A                   3000

40            B                  40000

 

I wrote a query with UNION and NOT IN.  But it is performing badly as these tables have massive data.

 

Could someone please let me know the best way to form this sql.

Thanks In advance.


Accepted Solutions
Teradata Employee

Re: help needed on combining two big tables

What about FULL OUTER JOIN

SELECT
COALESCE(Table1.ID, Table2.ID) as ID,
COALESCE(Table1.Name, Table2.Name) as Name,
COALESCE(Table1.Sal, Table2.Sal) as Sal
FROM Table1 FULL OUTER JOIN Table2
ON Table1.ID = Table2.ID

or perhaps UNION ALL / NOT EXISTS

 

SELECT ID, NAME, SAL FROM Table1
UNION ALL
SELECT ID, NAME, SAL FROM Table2
WHERE NOT EXISTS (SELECT 1 FROM Table1
WHERE Table1.ID = Table2.ID)
1 ACCEPTED SOLUTION
9 REPLIES
Teradata Employee

Re: help needed on combining two big tables

What about FULL OUTER JOIN

SELECT
COALESCE(Table1.ID, Table2.ID) as ID,
COALESCE(Table1.Name, Table2.Name) as Name,
COALESCE(Table1.Sal, Table2.Sal) as Sal
FROM Table1 FULL OUTER JOIN Table2
ON Table1.ID = Table2.ID

or perhaps UNION ALL / NOT EXISTS

 

SELECT ID, NAME, SAL FROM Table1
UNION ALL
SELECT ID, NAME, SAL FROM Table2
WHERE NOT EXISTS (SELECT 1 FROM Table1
WHERE Table1.ID = Table2.ID)
Junior Contributor

Re: help needed on combining two big tables

INSERT/SELECT the 1st table and use MERGE for the 2nd with WHEN NOT MATCHED only skipping rows with existing ids

Teradata Employee

Re: help needed on combining two big tables

Hi Dieter:

 

I think you mean:

 

"INSERT/SELECT the BIGGER table and use MERGE for the SMALLER with WHEN NOT MATCHED only skipping rows with existing ids"

 

;-)

 

Cheers.

 

Carlos.

Junior Contributor

Re: help needed on combining two big tables

No :-)

If ID present is in both the tables then pick only Table1 ID value. basically we should load complete Table1 data into final table and when it comes to table2 , process only the ID's which are not present in table 1.

Fan

Re: help needed on combining two big tables

Dieter,

Do MERGE ... WHEN NOT MATCHED has any advantage over WHERE NOT EXISTS solution suggested by Fred.

Junior Contributor

Re: help needed on combining two big tables

Of course, single access to the target table instead of two.

Plus no spool, if source & target PI match.

Teradata Employee

Re: help needed on combining two big tables

Yes, I overlooked that you were inserting to a new table. Dieter's suggestion is better, in that case.

Enthusiast

Re: help needed on combining two big tables

Hi Dieter,

Thank You so much for your time and help on this.

It worked and Performance Improved Significanlty.

 

Thanks again :)

 

Enthusiast

Re: help needed on combining two big tables

Thanks Fred for your Promt Reply on this.