≡ Menu

Using a Common Table Expression (CTE) with an UPDATE statement

For update part of ETL Process I have to write another Common table expression to get  all records where are changed at source to run update query on destination table


My update part of basic ETL using TSQL as


with ChangedRows 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 inner join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee


sourceEmp.empid = DestEmployee.SourceEmpId



sourceEmp.lastname <> DestEmployee.lastname

or sourceEmp.firstname <> DestEmployee.firstname

or sourceEmp.title <> DestEmployee.title

or sourceEmp.titleofcourtesy <>DestEmployee.titleofcourtesy

or sourceEmp.birthdate<>DestEmployee.birthdate

or sourceEmp.hiredate <> DestEmployee.hiredate

or sourceEmp.address <> DestEmployee.address

or sourceEmp.city <> DestEmployee.city

or sourceEmp.region <> DestEmployee.region

or sourceEmp.postalcode<>DestEmployee.postalcode

or sourceEmp.country <>DestEmployee.country

or sourceEmp.phone<>DestEmployee.phone

or sourceEmp.mgrid <> DestEmployee.mgrid



update  TSQLFundamentals2008DW.HR.DimEmployees

set    lastname = ChangedRows.lastname

,  firstname = ChangedRows.firstname

,  title = ChangedRows.title

,  titleofcourtesy =ChangedRows.titleofcourtesy

,  birthdate=ChangedRows.birthdate

,  hiredate = ChangedRows.hiredate

,  address = ChangedRows.address

,  city = ChangedRows.city

,  region = ChangedRows.region

,  postalcode=ChangedRows.postalcode

,  country =ChangedRows.country

,  phone=ChangedRows.phone

,  mgrid = ChangedRows.mgrid

from ChangedRows

where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId=ChangedRows.empid

Comments on this entry are closed.