CTE With (INSERT/ DELETE/ UPDATE) Statement In SQL Server

CTE shorthand for Common Table Expression used to simplify derived, nested and complex queries. In contrast, using CTE for writing & breaking complex logic, which is reusable and easily readable. CTE scope to single INSERT/ UPDATE/ DELETE statements, moreover, until the query lasts. Namely, CTE is similar to a view or derived table. CTE is not persisted in the database like temp tables. Additionally, writing recursive queries using CTE has another benefit.

In this Tech-Recipes post, we will walk through the usage of CTEs with data modification statement. Writing CTEs with INSERT/UPDATE/DELETE and MERGE statement. Likewise, CTEs can be used along with other statements including VIEWS, TRIGGER and generally SELECT.

CTE Syntax

;WITH CTE_Expression_Name [ (colname1, colname2, … , n) ]
AS
(CTE - Query Expression)

;WITH CTE_Expression_Name – Specifies name identifier for a CTE. This is mandatory and should be different from other CTE defined in the same scope.

[ (colname1, colname2, … , n) ] – Optional column names returned by CTE query expression. Good practice to have it for every CTE. The number of columns defined in this list should match with the number of columns returned by CTE – query expression.

(CTE – Query Expression) – Includes SELECT statement whose result will be populated as a CTE. Naming a column is compulsory in case of expression, and if the column name is not defined in the second argument.

Examples

To get started with CTE & data modification demo, use below query. Firstly, create a temp table (#SysObjects). Temp table populated with columns and data from sys.table, metadata table for demo purpose.

USE tempdb;

--Create Temp table from SQL Server metadata table.

DROP TABLE IF EXISTS #SysObjects;

CREATE TABLE #SysObjects
(
 [Name]		  varchar(1000)
,ObjectId	  int
,CreatedDate      datetime
);

1.

CTE – INSERT Statement In SQL Server

In the following query, using CTE, named SysObjectsCTE. This CTE includes a SELECT statement in query definition and referring to metadata table with column names specified. Furthermore, the result of CTE inserted into #SysObjects temp table.

--INSERT Statement With CTE
WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT TOP 100 name, object_id, create_date FROM SYS.objects
)
INSERT INTO #SysObjects 
(
Name, 
objectid, 
createdDate
)
SELECT Name, ObjectId, CreateDate
FROM   SysObjectsCTE; --CTE

SELECT * FROM #SysObjects;

As a rule, we can not have any other statement in between the declaration of CTE and the use of CTE. Having a statement in between these breaks the scope of CTE and raises an error. Using SELECT 1; query in between definition of CTE and use of CTE raises an error.

Msg 422, Level 16, State 4, Line 20
Common table expression defined but not used.

--INSERT Statement With CTE
WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT TOP 100 name, object_id, create_date FROM SYS.objects
)
SELECT 1; --In Between Statement Breaks the Scope of CTE
INSERT INTO #SysObjects 
(
Name, 
objectid, 
createdDate
)
SELECT Name, ObjectId, CreateDate
FROM   SysObjectsCTE; --CTE

2.

CTE – UPDATE Statement In SQL Server

Next, CTE with the UPDATE statement. Here, reusing the same CTE definition and joining the CTE result with #SysObjects temp table. UPDATE statement modifies Name column in #SysObjects by appending ‘Test’ to every name.

--UPDATE Statement With CTE

WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT name, object_id, create_date FROM SYS.objects
)
UPDATE #SysObjects
SET    Name = CONCAT('Test-',S.Name) 
FROM   #SysObjects as S INNER JOIN SysObjectsCTE as CTE ON CTE.ObjectId = S.ObjectId; --CTE

SELECT * FROM #SysObjects; 

3.

CTE – DELETE Statement In SQL Server

Another, CTE with a DELETE statement. Reusing the same CTE query definition. Later, joining CTE result with #SysObjects table and deleting rows having object_ids as odd numbers. Querying SELECT * FROM #SysObjects shows rows have been deleted.

--DELETE Statement With CTE 

WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT name, [object_id], create_date FROM SYS.objects
)
DELETE #SysObjects 
FROM #SysObjects as S INNER JOIN SysObjectsCTE as CTE ON S.ObjectId = CTE.ObjectId
WHERE  S.ObjectId % 2 = 1; 

SELECT * FROM #SysObjects; 

4.

CTEs – MERGE Statement In SQL Server

Similarly, CTE works with a MERGE statement. Using SysObjectsCTE as source table in MERGE statement inserts & updates all the missing and modified rows in the above examples. As a result, CTE can be used with MERGE statement as source data.

--Merge Statement With CTE
WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT name, [object_id], create_date FROM SYS.objects
)
MERGE #SysObjects as Target
USING SysObjectsCTE as Source ON (Target.ObjectId = Source.ObjectId) --CTE
WHEN MATCHED 
THEN UPDATE SET Target.Name = Source.Name,
				Target.ObjectId = Source.ObjectId,
				Target.CreatedDate = Source.CreateDate
WHEN NOT MATCHED BY TARGET
THEN INSERT ( Name, objectid, createdDate) VALUES (Source.Name, Source.ObjectId, Source.CreateDate)
OUTPUT $action,
	   DELETED.Name,
	   DELETED.ObjectId,
	   DELETED.CreatedDate,
	   INSERTED.Name,
	   INSERTED.ObjectId,
	   INSERTED.CreatedDate;

SELECT * FROM #SysObjects;

Summary

As a result, we have learned to use CTE (Common Table Expression) With Data modification statements (INSERT/DELETE/UPDATE and MERGE). If you like this post you may like to read through Tech-Recipes SQL Server archive posts for further reading.