Hi Colin, That's really awesome work, Thanks for your contribution. With this feature, we can do a lot of things in Cli instead of writing program in IDE..
On Wed, Aug 13, 2025 at 7:10 PM Colin Shi <shiz...@gmail.com> wrote: > Hi all, > > I’d like to let you know that a new feature was merged into IoTDB last > month (https://github.com/apache/iotdb/pull/15685). This feature > introduces > “INSERT INTO SELECT” statements based on the table data model, which allow > you to read data from existing source tables and insert it into another > existing target table. > > 1. Grammar > The SQL syntax is: > `INSERT INTO <table_name> [(column1, column2, ...)] <query>` > > Suppose there are two tables, t1 and t2, with the same schema. Here are > some usage examples: > * `INSERT INTO t2 SELECT * FROM t1` > * `INSERT INTO t2 TABLE t1` > * `INSERT INTO t2 (SELECT * FROM t1 WHERE x > 10)` > > 2. Restrictions > There are several syntax checks: > * The source and target tables should already exist. The user must have > SELECT privilege on the source tables and WRITE privilege on the target > table. > * The data types of the columns in the query must match those of the target > table. > * If specific columns of the target table are designated, the column list > must include the timestamp column and at least one field column. For > example: `INSERT INTO t2(time, x) SELECT time, x FROM t1` > > > 3. Usages > “INSERT INTO SELECT” statements can be used in various scenarios, such as: > * Data migration: Moving data from one table to another. > * Data transformation: Modifying data while inserting it into the target > table. > * Data aggregation: Inserting the results of complex queries (such as JOINs > or aggregations) into a summary table. > > In addition, “INSERT INTO SELECT” supports inserting data into the same > table from which it is queried. This makes it easy to generate or duplicate > data for testing purposes. For example: `INSERT INTO t1(time, x) SELECT > time + 86400000, x from t1` > > 4. Implementation > The implementation of “INSERT INTO SELECT” statements involves several key > components: > * Query parsing: Parses and validates the syntax of “INSERT INTO SELECT” > statements, and incorporates the query portion into an Insert statement. > * Query planning: Constructs an IntoNode in the logical plan and sets the > query plan as its child node. > * Execution: Transforms the IntoNode into an IntoOperator, which is > responsible for pulling data and inserting it into the target table. > > When multiple data regions are present, the IntoNode can be pushed below > the CollectNode, resulting in the following distributed execution plan: > +-----------+ > | OutputNode| > +-----------+ > | > +-----------------+ > | AggregationNode | > +-----------------+ > | > +-------------+ > | CollectNode | > +-------------+ > / \ > / \ > +----------+ +----------+ > | IntoNode | | IntoNode | > +----------+ +----------+ > | | > +---------------------+ +---------------------+ > | DeviceTableScanNode | | DeviceTableScanNode | > +---------------------+ +---------------------+ >
