Wednesday, March 26, 2014

Retrieve output inserted.id along with extra column from other table after inserting into some table

DECLARE @emp AS TABLE (id INT , firstname VARCHAR(50), lastname VARCHAR(50))
DECLARE @emp_staff AS TABLE (staffId INT, empId INT, created DATETIME)
DECLARE @staff AS TABLE (id INT IDENTITY NOT NULL,  FirstName VARCHAR(100), LastName VARCHAR(100))

INSERT INTO @emp VALUES (22,'John', 'Thompson')
INSERT INTO @emp VALUES (23,'Ricky', 'Bismen')
INSERT INTO @emp VALUES (24,'Jas', 'Poleam')
INSERT INTO @emp VALUES (25,'Andy', 'Ting')

--// Instead of below code Merge is a correct way to getting output id with extra column

--INSERT INTO @staff (FirstName, LastName)
--OUTPUT INSERTED.id, e.id ,GETDATE()
--INTO @emp_staff
--(
-- staffId,
-- empId,
-- created
--)
--SELECT firstname, lastname
--FROM @emp e


MERGE INTO @staff USING (SELECT id, FirstName, LastName FROM @emp e WHERE e.id > 23) e ON 1 = 0
WHEN NOT MATCHED THEN 
INSERT (FirstName, LastName) VALUES (FirstName, LastName) 
OUTPUT  INSERTED.id, e.id ,GETDATE() 
INTO @emp_staff
(
staffId,
empId,
created
);

SELECT * FROM @emp
SELECT * FROM @staff
SELECT * FROM @emp_staff

No comments: