Friday, October 21, 2016

Easy Way to get the output of the INSERT including the SELECT columns

/*Since the Output clause returns the rows affected by INSERT,UPDATE,DELETE and -When you want the output of the Insert statement
along with the columns from select statement used as source for  Insert, then you take this approach*/

CREATE TABLE #tblEmpTypeTmp (NewEmpTypeID INT,          ExistingEmpTypeID INT)

INSERT INTO MyDB.dbo.tblEmpType (EmpType,  EmpDecodeMethod)
OUTPUT inserted.EmpTypeID,
        INTO #tblEmpTypeTmp
SELECT cf.EmpType,
  FROM AnotherDB.dbo.tblEmpType AS cf

--The above approach errors out saying it cannot identify the column  'cf.EmpTypeID'
--So , take the below approach using Merge
CREATE TABLE #tblEmpTypeTmp (NewEmpTypeID INT,
                        ExistingEmpTypeID INT)
MERGE INTO MyDB.dbo.tblEmpType
USING AnotherDB.dbo.tblEmpType AS cf
ON 1 = 0
INSERT(EmpType, EmpDecodeMethod) Values(cf.EmpType, cf.EmpDecodeMethod)
Output inserted.EmpTypeID, cf.EmpTypeID INTO
#tblEmpTypeTmp(NewEmpTypeID, ExistingEmpTypeID);

select * from #tblEmpTypeTmp

No comments:

Post a Comment