Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aspnicole
Starting Member
2 Posts |
 Posted - 2002-09-03 : 19:50:48
|
| My question is a little bit complicated, thank you for your patience.The following is what i need to do...get a record setfor each record in record set excute an update sql statement based on one filed of the record excute an insert sql statement based on one filed of the recordnext...However, if any error terminates the process, some of records have been updated while some of them have not. So, I think about Transaction. Since we can't make it work in the following way:...get a record setbegain transactionfor each record in record set excute an update sql statement based on one filed of the record excute an insert sql statement based on one filed of the recordnextcommit or rollback transaction...What I could think about the solution is to save the outter recordset to an array, then use loop for that array. Does any body know it's good solution or not? I'm really frustrated. Thanks for your help.Nicole |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-04 : 03:57:32
|
| Could you insteadget a record set (into temp table?)begin tranexcute update sql statement on fields of type 1excute update sql statement on fields of type 2...excute insert sql statement on fields of type 1excute insert sql statement on fields of type 2...commit tranor maybe you could do it with just one update and one insert.Looping through the recordset shold be considered a last resort.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
aspnicole
Starting Member
2 Posts |
Posted - 2002-09-04 : 13:38:26
|
| Hi NR,I'm so appreciated your help on SQL Team forum(Transaction topic in Developer folder). I'll try to use temp table.Meanwhile, I'm thinking to change the solution from recordset in VB statement to cursor in stored procedures....begin transactionopen cursorfetch each sql statement 1 some assign statement sql statement 2 some assign statement sql statement 3nextcommit transaction...Do you think i should add error catch for each statement in cursor, including both sql statement and assign statement?If I only add error catch after each sql statement and some error occur in assign statement, what will happen? If so, I suppose the stored procedure will terminate. Do you think the transaction will rollback as well?Thank you so much for your quick response. Nicole |
|
|
|
DavidD
Yak Posting Veteran
73 Posts |
Posted - 2002-09-04 : 20:44:11
|
| Nicole, I think you are barking up the wrong tree, if you are using sql you have no need to cycle through a recordset to update all fields in a table. The statement is simply.Update tablenameset field = newvaluewhere somefield = whatever. Please post more specific details of your problem.RegardsDD |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-05 : 04:51:22
|
| Given that you aer wanting to do inserts and updates based on one field are you just trying to add new records that aren't there and update ones that are?update tbl2set ...from tbl1where tb1.pk = tbl2.pkinsert tbl2select ...from tbl1left outer join tbl2on tb1.pk = tbl2.pkwhere tbl2.pk is nullif you have dupolicate records in the input table just batch it up so that you only work on non duplicates and sort out the duplicates afterwards.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Transaction