Menu
I am working on a stored procedure which involves a delete and insert clause based on a previous query in the stored procedure.
I start with a WITH statement to build up some complicated queries into a simple CTE for use in the delete and insert statement.
However, I cannot seem to run both the insert and delete statement after the CTE.
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:
"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."
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?
689
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
A Common Table Expression (CTE) is a temporary result set that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement.
Advantage of Using CTE CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.
We run a delete against a CTE in SQL Server and the table's values are affected – this differs from taking data from a table to a temp table and removing data from the temp table. The source table still has the records. This logic does not carry over to using these with joined delete statements.
If you have same condition used on both DELETE and Insert Query, You may try this
WITH temp AS (SELECT id, name FROM myDBTable)
DELETE FROM thisTable
OUTPUT deleted.id, deleted.name into otherTable
WHERE .....(based on "temp")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
