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

Author  Topic 

Shep
Starting Member

19 Posts

Posted - 2013-07-22 : 14:39:39
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

549 Posts

Posted - 2013-07-22 : 14:51:39
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

30421 Posts

Posted - 2013-07-22 : 15:16:51
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
   

- Advertisement -