insert and update data for three tables at once

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 and update data for three tables at once

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2013-05-19 : 13:57:08

Hi all.

I create form based on Customers table. That form has Phone field which I would like to insert and update not only for Customers table and also for Demographic and Users tables. I know how create stored procedure to insert and update data for one table but how do I do it into three tables at the same time? I will appreciate for sample.

Thanks.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-19 : 14:49:25

You have to use three update commands.
Here is a way to do it:



CREATE PROC MyPhoneUpdateProc 
	@phonenum AS NVARCHAR(20),
	@userID as INT
	
AS 
UPDATE dbo.T1 SET PhoneNum = @phonenum where UserID = @userID;
UPDATE dbo.T2 SET PhoneNum = @phonenum where UserID = @userID;
UPDATE dbo.T3 SET PhoneNum = @phonenum where UserID = @userID;
GO

EXEC MyPhoneUpdateProc '123-456-7890', 1

eugz
Posting Yak Master

210 Posts

Posted - 2013-05-19 : 18:30:13

Hi MuMu88. Thanks for replay.

How to INSERT into three tables at the same time?

Thanks.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-19 : 21:52:39

quote:
Originally posted by eugz

Hi MuMu88. Thanks for replay.

How to INSERT into three tables at the same time?

Thanks.

If you are concerned about integrity of your data;
You can create a transaction, so if one of the update fails you can rollback.
Refer to the following link if you need help with transactions
http://stackoverflow.com/questions/15012886/how-to-rollback-or-commit-a-transaction-in-sql-server

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 00:17:47

-- See simple illustration


CREATE TABLE ABC1 (col INT)
CREATE TABLE ABC2 (col2 INT)

BEGIN TRY
BEGIN TRAN
	INSERT INTO ABC1 values(1)
	INSERT INTO ABC2 values(1/0)  -- leads to divide by zero exception
	COMMIT TRAN  -- If there is no error the TRY block then it commits the operation
END TRY
BEGIN CATCH
	SELECT @@ERROR  -- caught the error 
	ROLLBACK TRAN   -- Roll back all operations done in TRY block
END CATCH

SELECT * FROM ABC1
SELECT * FROM ABC2

DROP TABLE ABC1
DROP TABLE ABC2

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 00:17:47

-- See simple illustration


CREATE TABLE ABC1 (col INT)
CREATE TABLE ABC2 (col2 INT)

BEGIN TRY
BEGIN TRAN
	INSERT INTO ABC1 values(1)
	INSERT INTO ABC2 values(1/0)  -- leads to divide by zero exception
	COMMIT TRAN  -- If there is no error the TRY block then it commits the operation
END TRY
BEGIN CATCH
	SELECT @@ERROR  -- caught the error 
	ROLLBACK TRAN   -- Roll back all operations done in TRY block
END CATCH

SELECT * FROM ABC1
SELECT * FROM ABC2

DROP TABLE ABC1
DROP TABLE ABC2

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources