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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update two tables with one statement

Author  Topic 

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2013-04-05 : 11:44:50
I have two tables as below:

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?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-05 : 11:53:25
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).
Go to Top of Page

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2013-04-05 : 15:02:34
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?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-05 : 15:15:38
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.



Be One with the Optimizer
TG
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-05 : 15:18:01
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.
Go to Top of Page
   

- Advertisement -