CTE – With An Insert Statement
February 11, 2012 1 Comment
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.
DECLARE @NumTableVar TABLE( n INT);
;WITH numbers (n)
AS (SELECT 1
SELECT 1 + n
WHERE n < 1000)
INSERT INTO @NumTableVar (n)
OPTION (MAXRECURSION 0);
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.