IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Copy data from 2 tables to 1 table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 37
Location: Chennai

PostPosted: Thu Nov 21, 2013 12:20 am
Reply with quote

Hello,

Can someone please help me on the below request?

I have 2 old tables (table1 - 4 columns, table2 - 3 columns) and 1 new table (6 columns defined from the above 2 tables). I want to copy the data from old tables to new table using INSERT query. Please see my illustration below.

***Table1
Code:

ID     COLA     COLB      COLC
---    ------  -------   -------
A123   TEST1    100.00     P3
A124   TEST2    200.00     T1
A126   TEST3    300.00     X2


***Table2

Code:

ID     COLD      COLE     
---   ------    -------   
A001   BEN       X123
A002   JOHN      X123
A123   CHRIS     T245
A126   ALLEN     K243


Output I want to insert in the new table3

***Table3 (New Table)

Code:


ID     COLA     COLB    COLC    COLD     COLE
---    ------  ------  -------  ------  -------
A123   TEST1   100.00    P3     CHRIS    T245
A124   TEST2   200.00    T1
A126   TEST3   300.00    X2     ALLEN    K243
A001                            BEN      X123
A002                            JOHN     X123


I tried UNION query as shown below but it is not giving correct results.

Code:

INSERT TABLE3
SELECT X.COLA, X.COLB, X.COLC, Y.COLD, Y.COLE
   FROM TABLE1 X,
            TABLE2 Y
 WHERE X.ID = Y.ID
UNION
SELECT X.COLA, X.COLB, X.COLC, Y.COLD, Y.COLE
   FROM TABLE1 X,
            TABLE2 Y
 WHERE X.ID <> Y.ID

Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Nov 21, 2013 1:27 am
Reply with quote

you can use a full outer join , take the help of below link,

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_fullouterjoin.htm

Code:
load from your cursor name of cursor insert into your final table name


This will get you a better performance than above metod.
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 37
Location: Chennai

PostPosted: Thu Nov 21, 2013 3:30 am
Reply with quote

Thanks Rohit.

I tried using FULL OUTER JOIN but I'm not getting complete data loaded in the new table. The table2 contains value as '0' in ID field and If I want to exclude those records, I'm not getting data from table1 loaded into table3.

**Table1

Code:

ID     COLA     COLB      COLC
---    ------  -------   -------
A123   TEST1    100.00     P3
A124   TEST2    200.00     T1
A126   TEST3    300.00     X2


**Table2

Code:

ID     COLD      COLE     
---   ------    -------   
0         IAN       D356
0         CAR      C123
A001   BEN       X123
A002   JOHN      X123
A123   CHRIS     T245
A126   ALLEN     K243


I have used below query:
Code:

SELECT COALESCE(X.ID,Y.ID) AS ID,
            X.COLA,X.COLB,X.COLC,
            Y.COLD,Y.COLE
    FROM TABLE1 X
    FULL OUTER JOIN
    FROM TABLE2 Y
    ON X.ID = Y.ID
 WHERE Y.ID > 0


The output I'm seeing: A124 record is missing from Table1
Code:

ID     COLA     COLB    COLC    COLD     COLE
---    ------  ------  -------  ------  -------
A123   TEST1   100.00    P3     CHRIS    T245
A126   TEST3   300.00    X2     ALLEN    K243
A001                            BEN      X123
A002                            JOHN     X123


Any thoughts please.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Nov 21, 2013 8:12 am
Reply with quote

Please correct the syntax of full outer join..you have an additional FROM condition. Also instead of using WHERE condition you can add the same using AND in outer join condition. icon_cool.gif
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 37
Location: Chennai

PostPosted: Thu Nov 21, 2013 10:08 pm
Reply with quote

I'm seeing SQLCODE = -338, ERROR: AN ON CLAUSE IS INVALID
if I remove WHERE clause and add "AND" in outer join condition.

"An operator other than '=' is not allowed in a FULL OUTER JOIN or FULL
JOIN."

Code:

SELECT COALESCE(X.ID,Y.ID) AS ID,
            X.COLA,X.COLB,X.COLC,
            Y.COLD,Y.COLE
    FROM TABLE1 X
    FULL OUTER JOIN
    TABLE2 Y
    ON X.ID = Y.ID
    AND Y.ID > 0
;
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Nov 22, 2013 12:41 am
Reply with quote

Please try below query and let us know

Code:
SELECT COALESCE(X.ID,Y.ID) AS ID,
            X.COLA,X.COLB,X.COLC,
            Y.COLD,Y.COLE
    FROM TABLE1 X
    FULL OUTER JOIN
     (SELECT ID,COLD,COLE FROM  TABLE2
              WHERE ID >0) AS Y
    ON X.ID = Y.ID 
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts VB to VB copy - Full length reached SYNCSORT 8
No new posts SCOPE PENDING option -check data DB2 2
Search our Forums:

Back to Top