Hi friends,

I am working on a windows application. The purpose is to update
the "Production Database" with the data that is available in the datatable.


I have the following structure of the datatable :-

COLUMN-A COLUMN-B COLUMN-C COLUMN-D COLUMN-E
1 4345 10 20 30
2 4231 39 43 27
3 54 443 55
4 4333 32 23 12


I have to update the Column-C,D and E values in the Production
database( single database table) against the values of COLUMN-A AND B.

Column-C,D and E represents the same column in the database table.

So this is sure I will be sending as in param for a stored procedure
as an array params.

But this can be possible for one record at a time.

I dont want to hit the database again and again in a loop for
updating all the rows.

What could be the better approach to achieve the result in one go.

Please reply.

Thanks.







[Munish]

If you wrap all the changes in a single transaction and use a single connection it should be quite quick. Have you tried it and its too slow?

/- Sam Judson : Wrox Technical Editor -/

Well I have also thought the same and thats the most feasible
approach but the problem is that the updation has to be done on
production database and table which has to be updated is a transaction
table. There is a possibility that the time I read the values would
change by some other user and the values may be not the same at the time
of updation.

So I thought of executing a stored procedure and updation one row at
a time with row locking mechanism. Means the reading and updation
simulataneously so the integrity of data wouldnt be affected.

But also at the same time it is not feasible to hitting the database
by executing the SP for each row.

What could be a better approach.

Thanks for the response samjudson.

The issue to be consider is the data itegrity and not the speed.


[Munish]

Updating the transaction table in production server is always affects performance and data integrity.Try to schedule to update the db in night time, that's when user not hitting the db.

And u can try as follow,

update table1 set table1.col2=table2.col2
from table1,table2
where table1.col1=table2.col1


Thanks,
Viji

Thanks Viji.

Update statement was never been a problem which has to come in the final
step but the problem is now is to finalise the best approach.

You are correct that updation process should be done at night when
low data usage/transactions are expected but at the same time,
should I consider the updation process by updating a datatable to
production database as I stated in my first post or should I use a
stored procedure, which keeps hitting the database for each row updation. The reason behind doing the updation for each row is that I
also wants to track the errors if any for each row and continues the
updation with the next row.






[Munish]

You can still wrap calls to a stored procedure in a transaction you know.

You can't have the level of control you want (i.e. doing each row one at a time and handling errors one at a time) without doing the updates one at a time.

/- Sam Judson : Wrox Technical Editor -/