Using a Common Table Expression (CTE) with an INSERT INTO statement

My current assignment was to write ETL Process using TSQL.  So I have to get all rows which are newly added to Source Table and are not part of destination table. I wrote simple Common Table Expression to get all rows and insert into destination table.

So part of ETL Process using CTE as


with NewRows as


SELECT     sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address,, sourceEmp.region, sourceEmp.postalcode,,, sourceEmp.mgrid,DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp left join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee


sourceEmp.empid = DestEmployee.SourceEmpId

where DestEmployee.SourceEmpId is null


INSERT INTO [TSQLFundamentals2008DW].[HR].[DimEmployees]
















NewRows.lastname, NewRows.firstname, NewRows.title, NewRows.titleofcourtesy, NewRows.birthdate,

NewRows.hiredate, NewRows.address,, NewRows.region, NewRows.postalcode,,, NewRows.mgrid,

NewRows.empid  from NewRows)


Advisory software engineer, Software engineer, Last 4 years focus on Dynamics Ax 2012. MVP and Microsoft Certified trainer for Dynamics AX.