Archives of the TeradataForum
Message Posted: Sun, 06 Mar 2005 @ 16:11:55 GMT
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;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|