≡ Menu




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)

Comments on this entry are closed.