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

on

sourceEmp.empid = DestEmployee.SourceEmpId

where

 

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