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/Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shep
Starting Member

USA
19 Posts

Posted - 07/22/2013 :  14:39:39  Show Profile  Reply with Quote
Can you insert a row into one table and update a row into another table in the same statement?

I need to update a row with a column name 'Status' on table 'RequestForms' and also need to insert a row into a different table named 'Approvals' with the following columns:
RequestFormID, Approval (Y/N), ApprovalDate, ManagerEmployeeID, Comments

Both tables will update depending on which of 2 radio buttons is selected. radApproved or radDenied

An examples to this would be well appreciated.
Thanks,
JS

J.E.Shepler

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/22/2013 :  14:51:39  Show Profile  Reply with Quote
You implement a transaction.
http://msdn.microsoft.com/en-us/library/windows/desktop/ms687099(v=vs.85).aspx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  15:16:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The solution is composable dml.
INSERT	dbo.Approvals
	(
		RequestFormID,
		[Approval (Y/N)],
		ApprovalDate,
		ManagerEmployeeID,
		Comments
	)
SELECT	RequestFormID,
	[Approval (Y/N)],
	ApprovalDate,
	ManagerEmployeeID,
	Comments
FROM	(
		MERGE	dbo.RequestForms AS tgt
		USING	(
				VALUES	(@Approval, @RequestFormID)
			) AS src(Approval, RequestFormID) ON src.RequestFormID = tgt.RequestFormID
		WHEN	MATCHED
				THEN	UPDATE
					SET	tgt.[Approval (Y/N)] = src.Approval
		OUTPUT	tgt.RequestFormID,
			tgt.[Approval (Y/N)],
			tgt.ApprovalDate,
			tgt.ManagerEmployeeID,
			tgt.Comments
	) AS d;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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