Using a Common Table Expression (CTE) with an Delete statement, Delete Part of ETL Process

Delete Part of ETL Process

If any row is deleted at source table, but destination table rows exits. So I did to make a inner join with source table with destination table I used right join in this case, so I got all rows from destination which I placed at right side of query. The result set I got with null at left side form source table. I filter the result set and got all the Keys which have null at source table. And call delete at destination on filter keys.

My common table Expression for Delete will be as follow

with DeleteRows as

(

SELECT     sourceEmp.empid, DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp right join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

)

--select SourceEmpId from DeleteRows where empid is null

delete from TSQLFundamentals2008DW.HR.DimEmployees

where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId in (

select SourceEmpId from DeleteRows where empid is null)