How does upsert or Update exisiting or insert new recs work
Posted: Fri Nov 06, 2009 1:11 pm
I have two parallel streams in a server job writting records to common table. The two streams have the same set of keys (Key 1,Key 2), but populate some over lapping fields (LOAD_DATE) and two stream specific fields (SRC & TGT).
The problem is, lets say there are 50 records from stream 1 and 50 records from stream two, Stream 1 is able to update or insert all 50 records and Stream 2 manages to update or insert only 48. Two records are not updated.
Data Stream 1
Key 1, Key 2, SRC_AMT, SRC_PRODUCT, LOAD_DATE
1 A 100.00 Type 1 Nov 1st, 2009
2 A 200.00 Type 2 Nov 1st, 2009
....
....
Data Stream 2
Key 1, Key 2, TGT_AMT, TGT_PRODUCT, LOAD_DATE
1 A 100.00 Type 1 Nov 1st, 2009
2 A 200.00 Type 2 Nov 1st, 2009
....
....
When I check in the table
Key 1, Key 2, SRC_AMT, SRC_PRODUCT, TGT_AMT, TGT_PRD, LOAD_DT
1 A 100.00 Type 1 100.00 Type 2 Nov 1st, 2009
2 A 200.00 Type 2 (null) (Null) Nov 1st, 2009
there are No warning messages in the job logs, indicating any errors or rejects.
So back to my question, how does upsert or Update existing or Insert new rows work in the back end?
1. Does it execute a select statement on the table to check if the record exists, if it does then insert else send in update statement?
2. Does it simply execute the update statement, if it gives a negative return code then execute the insert statement?
Thank you,
a novice..
The problem is, lets say there are 50 records from stream 1 and 50 records from stream two, Stream 1 is able to update or insert all 50 records and Stream 2 manages to update or insert only 48. Two records are not updated.
Data Stream 1
Key 1, Key 2, SRC_AMT, SRC_PRODUCT, LOAD_DATE
1 A 100.00 Type 1 Nov 1st, 2009
2 A 200.00 Type 2 Nov 1st, 2009
....
....
Data Stream 2
Key 1, Key 2, TGT_AMT, TGT_PRODUCT, LOAD_DATE
1 A 100.00 Type 1 Nov 1st, 2009
2 A 200.00 Type 2 Nov 1st, 2009
....
....
When I check in the table
Key 1, Key 2, SRC_AMT, SRC_PRODUCT, TGT_AMT, TGT_PRD, LOAD_DT
1 A 100.00 Type 1 100.00 Type 2 Nov 1st, 2009
2 A 200.00 Type 2 (null) (Null) Nov 1st, 2009
there are No warning messages in the job logs, indicating any errors or rejects.
So back to my question, how does upsert or Update existing or Insert new rows work in the back end?
1. Does it execute a select statement on the table to check if the record exists, if it does then insert else send in update statement?
2. Does it simply execute the update statement, if it gives a negative return code then execute the insert statement?
Thank you,
a novice..