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)


Ali Raza Zaidi, Microsoft ERP Consultant, Currently working for Business Experts Gulf in United Arab Emirates. Specialized in HR & Payroll, Supply Chain and Financials modules.