But this does not work, because the CTE is not found in the second operation (INSERT command). I've never used CTE's before, but I was under the impression that one of the reasons for using CTEs was to eliminate the need to define a recordset more than once in a procedure. I've recently discovered that the CTE is only valid for the first operation after it is defined, which is why it works for either the DELETE or the INSERT, when I remark out the other one, but does not work as indicated above.
;WITH Recent_Production (FieldList) AS ( SELECT FieldList FROM ProductionTable ) DELETE FROM tbl_Reports WHERE Exists (SELECT 1 FROM Recent_Production WHERE Entity_ID = tbl_Reports.Entity_ID AND ProductionDate = tbl_Reports.docDate) INSERT INTO tbl_Reports SELECT * FROM Recent_Production
From novice to tech pro — start learning today.