≡ Menu

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

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


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)

Comments on this entry are closed.