CTE – With An Insert Statement

Queries with Common table expressions (CTE) are made up of two parts, the CTE part, and the SQL that references the CTE.  In preparation for SQL Saturday, the question came up of can you use an INSERT or UPDATE statement with a CTE.  Referring to the documentation I confirmed that using an insert or update inside of the CTE is invalid, but you can use an insert or update statement outside of the CTE.

 

For Example.


DECLARE @NumTableVar TABLE( n INT);

 

;WITH numbers (n)
AS (SELECT 1
UNION ALL
SELECT 1 + n
FROM   numbers
WHERE  n < 1000)

INSERT INTO @NumTableVar   (n)
SELECT n
FROM   numbers
OPTION (MAXRECURSION 0);


SELECT *
FROM   @NumTableVar;



When run confirms that you can use the insert statement with a CTE, but not inside of a CTE.

 

This would be very useful if you had just created a table and wanted to fill it up quickly for testing purposes.