带插入和删除的通用表表达式

Question

我正在处理一个存储过程，它涉及基于存储过程中先前的查询的delete和insert子句。

我从with语句开始，将一些复杂的查询构建为一个简单的CTE，以便在delete和insert语句中使用。

但是，在CTE之后，我似乎不能同时运行insert和delete语句。

示例代码：

WITH temp AS (SELECT id, name FROM myDBTable)
DELETE FROM thisTable WHERE .....(based on "temp")
INSERT INTO otherTable (id, name) FROM (based on "temp")

根据MSDN

“CTE后面必须有引用部分或全部CTE列的单个SELECT、INSERT、UPDATE、MERGE或DELETE语句。还可以在CREATE语句中指定CTE作为视图定义SELECT语句的一部分。”

上面写着“单身”..statement。我不能做多次选择/删除/等等吗？如果没有，有没有办法解决这个问题？

单独地，这两个查询都可以工作，但是否可以在一个存储过程中使用该CTE运行？

Answer 1

如果在DELETE和Insert查询中使用相同的条件，则可以尝试以下操作

WITH temp AS (SELECT id, name FROM myDBTable)
DELETE FROM thisTable 
OUTPUT deleted.id, deleted.name into otherTable 
WHERE .....(based on "temp")

Answer 2

不能在多个语句中引用CTE。不过，您可以使用临时表或表变体。

您还希望将所有这些都包含在事务中，您肯定不希望执行删除，然后让insert失败而不是回滚delete。

Answer 3

您可以使用公共表表达式的合并，我已经提供了一个示例说明如何在下面实现这一点。

示例：

;WITH cte AS
(
 SELECT id,
      name
 FROM [TableA]
)

MERGE INTO [TableA] AS A
 USING cte
    ON cte.ID = A.id

  WHEN MATCHED 
  THEN DELETE

  WHEN NOT MATCHED 
  THEN INSERT
VALUES(cte.name);