tencent cloud

Feedback

INSERT INTO

Last updated: 2024-08-07 17:25:30

    Description

    Supported engines: Presto and SparkSQL
    Applicable table: Native Iceberg tables and external tables
    Purpose: Insert the results of a SELECT query on the source table into the target table as new rows.

    Statement

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

    Parameter

    table_identifier: Table name in the three-part format. Example: catalog.database.table.
    partition_spec: Partitioning column name and value Example: dt='2021-06-01'
    column_list: All the columns
    query: A general Select query statement     
    1.1 a SELECT statement
    1.2 a TABLE statement

    Example

    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`)
    
    
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support