CTE – With An Insert Statement

Download PDF

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.



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

INSERT INTO @NumTableVar   (n)
FROM   numbers

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.

Posted in CTE, SQL Saturday Tagged with: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *


SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the SteveStedman.com blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions