v.24.10New Feature

Support for CTE in INSERT Statements

Official CH changelog text
Support for CTE in the form of WITH ... INSERT, as previously we only supported INSERT ... WITH .... #70593 (Shichao Jin).
Added support for Common Table Expressions (CTE) in the form of WITH ... INSERT, complementing the existing support for INSERT ... WITH syntax.

Why it matters

This feature allows users to write queries where the CTE is defined before the INSERT statement, improving query readability and flexibility. It resolves the limitation of only being able to use CTEs after INSERT, enabling more intuitive and versatile data insertion workflows.

How to use it

Users can now place the WITH clause before the INSERT statement in their SQL queries, for example:

WITH cte AS (<query>)
INSERT INTO <table> SELECT * FROM cte

Related resources

PreviousNext