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, or DELETE statement.

This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

A common table expression can include references to itself.

This is referred to as a recursive common table expression.

 

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  [ WITH 
    
      [ ,...n ] ]  
     
      ::=      expression_name [ ( column_name [ ,...n ] ) ]      AS      ( CTE_query_definition )
     
    

 

 

Remarks

A CTE must be followed by a single SELECT, INSERT, UPDATE, 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.

 

 

Error

Incorrect syntax near the keyword 'with'.

If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.