Replace existing rows
Moderators: chulett, rschirm, roy
Replace existing rows
I have job which reads data from a abc.txt and loads into oraOCI i am using the update action -Replace existing rows into TableA which has 2 key columns.
But now i need to delete the rows depending on only one key and than insert the rows.so how can do this most efficiently.
Thanks
sheema
But now i need to delete the rows depending on only one key and than insert the rows.so how can do this most efficiently.
Thanks
sheema
What you expect the output should be for this case?
If there is any record with 1001, it should be replaced with these two records?
And by the way, the database keys has not been changed, only are just marking the key in datastage as one key.
But to be more comfortable, you can do a lookup to the target and replace the rows with the input rows, if not found, insert it.
If there is any record with 1001, it should be replaced with these two records?
And by the way, the database keys has not been changed, only are just marking the key in datastage as one key.
But to be more comfortable, you can do a lookup to the target and replace the rows with the input rows, if not found, insert it.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Have you tried the option "Replace Existing Row completely" with single filed marked as Key in Datastage.
Observer the SQL generated, a DELETE statement will be issued first with the condition, Where Col1 = :1 (Single field).
Later Insert statement.
Observer the SQL generated, a DELETE statement will be issued first with the condition, Where Col1 = :1 (Single field).
Later Insert statement.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
yes,that is working fine.I need another clarification
Previously we had 2 jobs
Job1,we read from the source file and than
we used a aggregator to group by on key1 and delete the records from tabl1.
Job2,we read from the same source file and insert the rows into tabl1.
So instead of 2 jobs i am combining them into a single job,by using the update action as 'Replace existing rows completely' and as you suggested by taking a single key column in oraOCI.i just want to make sure that i am right by replacing 2 jobs with a single job.
Previously we had 2 jobs
Job1,we read from the source file and than
we used a aggregator to group by on key1 and delete the records from tabl1.
Job2,we read from the same source file and insert the rows into tabl1.
So instead of 2 jobs i am combining them into a single job,by using the update action as 'Replace existing rows completely' and as you suggested by taking a single key column in oraOCI.i just want to make sure that i am right by replacing 2 jobs with a single job.
I don't get you full business logic on why is it all been done.
Just be aware that, if your input have more that 1 row with the same key value say 1001, you will end up in inserting only the latest one. Where I guess you need to insert both of them.
Just be aware that, if your input have more that 1 row with the same key value say 1001, you will end up in inserting only the latest one. Where I guess you need to insert both of them.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Make sure you understand what Kumar is saying. A second occurance of a key value in your input source will delete the first value that you just inserted!kumar_s wrote:Just be aware that, if your input have more that 1 row with the same key value say 1001, you will end up in inserting only the latest one. Where I guess you need to insert both of them.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Technically, no. The end result is the same, but what actually happens would be this:
1) 1001,wed. Any existing records with 1001 in that field will be deleted then this record will be inserted.
2) 1001,mnb. Any existing records with 1001 in that field (now meaning the "1001,wed" record) will be deleted then this record will be inserted.
1) 1001,wed. Any existing records with 1001 in that field will be deleted then this record will be inserted.
2) 1001,mnb. Any existing records with 1001 in that field (now meaning the "1001,wed" record) will be deleted then this record will be inserted.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers