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

I am working on a stored procedure which involves a delete and insert clause based on a previous query in the stored procedure.

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

I start with a WITH statement to build up some complicated queries into a simple CTE for use in the delete and insert statement.

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

However, I cannot seem to run both the insert and delete statement after the CTE.

但是,我似乎无法在CTE之后同时运行insert和delete语句。

Sample Example Code:

示例代码:

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

According to the MSDN:

根据MSDN:

"A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view."

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

It says a "single" ..statement. Am I not able to do multiple Select/Delete/etc.? If not, is there any way around this?

它说“单一”......陈述。我无法进行多次选择/删除/等等。如果没有,有什么办法吗?

Individually both queries work, but can you have both run using that CTE in a single stored procedure?

单独两个查询都可以工作,但是您是否可以在单个存储过程中使用该CTE运行?

3 个解决方案

#1

If you have same condition used on both DELETE and Insert Query, You may try this

如果在DELETE和Insert Query上使用相同的条件,则可以尝试此操作

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

#2

CTEs cannot be referenced in multiple statements. You could use a temp table or table varaible though.

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

You also want to enclose all of this in a transaction, you certainly wouldn't want to perform the delete and then have the insert fail and not rollback the delete.

您还希望将所有这些包含在事务中,您当然不希望执行删除,然后使插入失败并且不回滚删除。

#3

You could use a merge with the Common Table Expression, I have provided an example of how this could be achieved below.

你可以使用Common Table Expression的合并,我提供了一个如何在下面实现这一点的例子。

Example:

;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);

#1