BorrowRates ID YearID MinimumValue MaximumValue Rate
CustomerRate ID CustomerID BorrowRateID Yield Commission
As part of an application, both tables have rows inserted when a customer borrows money.
In other words the BorrowRates table insert function returns an ID (Scope Identity). That ID is then used as the BorrowRateID on the CustomerRate table which then has its own insert function which includes info for the remainder fields to complete (from other areas of the app) then insert for CustomerRate.
If i wanted to update these two tables how would i be doing it in one attempt?
I think what you are looking for is called a Transaction (http://msdn.microsoft.com/en-us/library/ms188929.aspx). You BEGIN a transaction and update each table. If there are no errors, you COMMIT the transaction. If there was an error, you ROLLBACK the transaction. This ensures that the database is consistant (ACID).
Thanks. From my reading so far on SQL Transactions it seems i need an UPDATE statement first before even creating the transaction. So that leaves with me creating the UPDATE statement first but based on the tables above?
You can't write to more than one table per statement. Well, you can if you insert a partitioned view or use an OUTPUT INTO clause but those are not what you are talking about.
What do you mean when you say, "how would i be doing it in one attempt?" ? Lamprey probably interpreted your meaning correctly which I interpreted as you want both inserts to succeed or neither to succeed. All or nothing. Is that correct? If so that means you should put both statements inside an explicit user transaction. A transaction does not require a preceding update statement. Not sure where you got that.
I think you are reading it incorrectly. The sequence would be: 1) BEGIN TRAN 2) Do the first update 3) Do the second update 4) COMMIT or ROLLBACK. You may want to wrap the statements in a try/catch block as well. Take a look at examples (B) or (C) on this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx . You may want to follow that pattern.
One thing to be aware, if it matters to you, is that even if the transaction gets rolled back, if the identity has been incremented that will not revert back.