Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sun, 06 Mar 2005 @ 16:11:55 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Update/Insert (Upsert)
From:   kumar28

Thanks for the reply.

As suggested I tried the Update and Insert seperately but was very slow as the table I am updating/inserting is big and growing. It took about 30 minutes to do the update/insert.

I found another way and tried something with COALESCE below...looks like it worked but is tedious and have to go few steps to do a simple process.....But this process turned out to be much much faster than Insert/Select.

Do you see if there is a problem with this process.

     target_tbl--> is the table to be updated and Inert new data.
     newdata_tbl ...> has new data....

     create table temp_tbl
     as target_tbl
     with no data;

     /* This does the Update and Insert new data  in temp_table  */
     insert into temp_tbl
     newdata_tbl full outer join
     on(newdata_tbl.col1 = target_table.col1
      and newdata_tbl.col2 = target_table.col2);

     delete from target_tbl; --->This is the target table to be updated and inserted

     insert into target_table
     select * from temp_tbl;

     drop table temp_tbl;



  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023