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
 General SQL Server Forums
 New to SQL Server Programming
 insert and update data for three tables at once
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eugz
Posting Yak Master

195 Posts

Posted - 05/19/2013 :  13:57:08  Show Profile  Reply with Quote
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.

Edited by - eugz on 05/19/2013 14:00:46

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/19/2013 :  14:49:25  Show Profile  Reply with Quote
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 



Edited by - MuMu88 on 05/19/2013 14:51:47
Go to Top of Page

eugz
Posting Yak Master

195 Posts

Posted - 05/19/2013 :  18:30:13  Show Profile  Reply with Quote
Hi MuMu88. Thanks for replay.

How to INSERT into three tables at the same time?

Thanks.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/19/2013 :  21:52:39  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 05/20/2013 :  00:17:47  Show Profile  Reply with Quote
-- 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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 05/20/2013 :  00:17:47  Show Profile  Reply with Quote
-- 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
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