Hi, First job is inserting 10 records in one table with increment of Temp_Id like 1,2,3.......10
The second job need to insert 20 more records in the same table with increment of Temp_Id by taking the last value of the first insert like 11,12,......30
1. Create a sequence/trigger within your database to automatically assign a value to the Temp_id when the values are loaded; or
2. Use a bit a sql code to retrieve the current max(temp_id) from the that table. Pass this value to a surrogate key generator stage and use that to create your temp_id, using the max(temp_id) as your starting point (pass it in as a parameter).
Last edited by ShaneMuir on Fri May 22, 2009 7:59 am, edited 1 time in total.
OK... how are you generating this "temp_id" value currently? Investigate the use of the Surrogate Key Generator stage and the use of a "state file", that's the "official" way to handle stuff like this.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Earlier i did this sequence number by using stage variable in transformer.but now i need to take max value of that and need to process it again to another job/table.
Earlier i did this sequence number by using stage variable in transformer.but now i need to take max value of that and need to process it again to another job/table.
Can you not do something like Shane posted as #2 above - retrieve the max value using SQL and pass that as a job parameter that is the starting value for the job run.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Yes i am getting the max val of that col when executed but not in datastage source stage. server stage is working fine only the issue with parallel oralce stage.
