SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update two tables with one statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 04/05/2013 :  11:44:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4344 Posts

Posted - 04/05/2013 :  11:53:25  Show Profile  Reply with Quote
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 - 04/05/2013 :  15:02:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5907 Posts

Posted - 04/05/2013 :  15:15:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 04/05/2013 :  15:18:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000