Archives of the TeradataForum

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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
     select
     coalesce(newdata_tbl.col1,target_table.col1)
     ,coalesce(newdata_tbl.col2,target_table.col2)
     ,coalesce(newdata_tbl.col3,target_table.col3)
     ,coalesce(newdata_tbl.colN,target_table.colN)
     from
     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;

Thanks

Raj

	<Prev	Next>		<<First	<Prev	Next>	Last>>