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,

        cf.EmpTypeID 

        INTO #tblEmpTypeTmp

SELECT cf.EmpType,

        cf.EmpDecodeMethod

  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

WHEN NOT MATCHED THEN

INSERT(EmpType, EmpDecodeMethod) Values(cf.EmpType, cf.EmpDecodeMethod)

Output inserted.EmpTypeID, cf.EmpTypeID INTO

#tblEmpTypeTmp(NewEmpTypeID, ExistingEmpTypeID);

select * from #tblEmpTypeTmp