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.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp left join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

where DestEmployee.SourceEmpId is null

)

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

([lastname]

,[firstname]

,[title]

,[titleofcourtesy]

,[birthdate]

,[hiredate]

,[address]

,[city]

,[region]

,[postalcode]

,[country]

,[phone]

,[mgrid]

,[SourceEmpId])

(select

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

NewRows.hiredate, NewRows.address, NewRows.city, NewRows.region, NewRows.postalcode, NewRows.country, NewRows.phone, NewRows.mgrid,

NewRows.empid  from NewRows)

alirazazaidi

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.