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
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:
Post a Comment