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