Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Yak Posting Veteran

65 Posts

Posted - 04/05/2013 :  11:44:50  Show Profile  Reply with Quote
I have two tables as below:



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?


Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/05/2013 :  11:53:25  Show Profile  Reply with Quote
I think what you are looking for is called a Transaction ( 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

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

Flowing Fount of Yak Knowledge

6065 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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 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: . 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  
 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.02 seconds. Powered By: Snitz Forums 2000