Insert/Update two tables at the same time

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

General SQL Server Forums

New to SQL Server Programming

Insert/Update two tables at the same time

Author

Topic

tobtob
Starting Member

7 Posts

Posted - 2010-12-02 : 09:36:58

I have a table (Users) that I need to update in two different databases (Same MSSQL Server). Basically, when a new user registers I want to add the record to the users table in both tables/db's. When a user "redeems an activation code" I want to UPDATE that field in both tables/DB's.
Is there anything special to do other than simply issue the statements twice.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 09:44:14

Depends what you want to do on failure.
You could issue both statements in a transaction.
You could have a trigger on one table that updates the other.

In both of these cases the transaction will be held until both tables are updated and a rollback will cause both to fail.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sachin.Nand

2937 Posts

Posted - 2010-12-02 : 10:21:13

OR set a up a Transaction replication.

PBUH

tobtob
Starting Member

7 Posts

Posted - 2010-12-02 : 11:17:58

Thanks for the help but I am truly a beginner with SQL. I looked into transactions and triggers slightly but it would be very helpful if you could supply some code examples.
The transactional replication seemed complicated to me. It might be overkill for our situation. (not much traffic)
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 11:34:56

just enacpsult the statements in a transaction
Also include error handling
Much more control if this is in a strored procedure

create proc s_updateWhatever
@parms...
as

begin try
begin tran
statement 1
statement 2
commit tran
end try
begin catch
rollback tran
-- error handling
end catch

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources