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)