Help & Documentation>Data Lake Compute

INSERT INTO

Last updated: 2024-01-12 10:27:46

On this page:

Note

Supported kernels: Presto, SparkSQL.
Applicable table scope: Native Iceberg tables, external tables.
Purpose: Supports inserting the results of SELECT queries run on the source table as new rows into the target table.

Syntax

[ WITH with_query [ , ... ] ]
INSERT {INTO [<TABLE>]| TABLE} table_identifier [ partition_spec ] [ ( column_list ) ]
    { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }

Category

table_identifier: Specifies the table name, supports three-part notation, for example: catalog.database.table.
partition_spec: Partition column and value. For example, dt='2021-06-01'.
column_list: All of the columns.
Query: A generic Select query statement.     
1.1 a SELECT statement
1.2 a TABLE statement

Sample

INSERT INTO orders SELECT * FROM new_orders;
INSERT INTO cities VALUES (1, 'China');
INSERT INTO nation (nationkey, name, regionkey, comment)
VALUES (26, 'POLAND', 3, 'no comment');

-- INSERT INTO partition
INSERT INTO students PARTITION (student_id = 444444) SELECT name, address FROM persons WHERE name = 'dlc'
INSERT INTO students PARTITION (student_id = 11215017) (address, name) VALUES  ('Shen zhen, China', 'tester')

-- Insert Using a TABLE Statement
INSERT INTO students TABLE visiting_students;

-- with
WITH tmp1 AS ((SELECT *
FROM catalog1.db1.tbl1)), tmp2 AS ((SELECT *
FROM tbl2))
INSERT INTO catalog1.db2.tbl1
(SELECT col1, col2
FROM tmp1 a
INNER JOIN tmp2 b ON a.col1 = b.col2)


INSERT INTO catalog1.db2.tbl1
WITH tmp1 AS ((SELECT *
FROM catalog1.db1.tbl1)), tmp2 AS ((SELECT *
FROM tbl2))
(SELECT col1, col2
FROM tmp1 a
INNER JOIN tmp2 b ON a.col1 = b.col2)
中国站 - English