Lookup Insert Update on same table in a job

rajan.n · Post by **rajan.n** » Fri Dec 11, 2009 3:32 pm

Hi all ,

I will have to do a Lookup ,Insert and update all on same table Irrespective of volume size ( Actually volume is 50k records), what will be the best design approach can we follow.

1) Can we do Lookup , Update , Insert all on one table and in one job ? If so what will be the pre steps we need to take care.
2) We can Look up the table and write Inserts and update saperatly to a Datasets and having 2 different jobs where one do Insert to table and other Update to table.

Can some one please give me the pros and cons of both approaches too.

Thanks
Rajan

ray.wurlod · Post by **ray.wurlod** » Fri Dec 11, 2009 5:29 pm

Option 2 will not suffer from the queries locking the tables that you are attempting to update (assuming the jobs are run consecutively).

rajan.n · Post by **rajan.n** » Fri Dec 11, 2009 5:36 pm

Thank you Ray,
What if I wanted to go by option 1 and overcome the locking problem what would be the workarround I need to takecare of ? can you please let me know. Thanks.
Rajan

ray.wurlod · Post by **ray.wurlod** » Fri Dec 11, 2009 6:15 pm

Use a server job in which you can interpose a blocking operation (these are banned in parallel jobs). Otherwise there are no guarantees - among other things it depends on the level at which you are locking, the number of rows per transaction and the array size.

rajan.n · Post by **rajan.n** » Mon Dec 14, 2009 8:45 am

ray.wurlod wrote:Use a server job in which you can interpose a blocking operation (these are banned in parallel jobs). Otherwise there are no guarantees - among other things it depends on the level at which you are locking, the number of rows per transaction and the array size.

Thanks Ray,
Will this imply to the following scenario too?

1) Lookup and target (Insert) are same tables in a single job
2) I will create a diff job for only update.

we are doing this in parallel jobs and Here I will have 2 jobs instead of 3 jobs.