May 31, 2011 at 5:30 am
Hi All,
Here is my question i would like to insert some values into two different table with single sql transaction..
Thanks in advance
seshu
May 31, 2011 at 5:36 am
Begin Transaction
Insert into Table1 (<column list>) Values (<Values list>)
Insert into Table2 (<column list>) Values (<Values list>)
Commit Transaction
You should add error handling if it's for real usage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2011 at 8:37 am
If you don't care to have special handling of the error case:
SET XACT_ABORT ON -- in case of error, there will be implicit rollback and procedure stop
Begin Tran
Insert into Table1 (<column list>) Values (<Values list>)
Insert into Table2 (<column list>) Values (<Values list>)
IF @@TRANCOUNT > 0 Commit Tran
If you like to handle the error case and do something when it happens:
begin try
Begin Tran
Insert into Table1 (<column list>) Values (<Values list>)
Insert into Table2 (<column list>) Values (<Values list>)
IF @@TRANCOUNT > 0 Commit Tran
end try
begin catch
IF @@TRANCOUNT > 0 rollback tran
-- Optionally, you can log the error, rethrow it, or throw something else
SELECT ErrorMessage = ERROR_MESSAGE()
end catch
May 31, 2011 at 3:26 pm
If you would like to use one select statement to insert into 2 tables at the same time you can setup view with trigger and write the insert statements within the trigger.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply