Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



INSERT ... SELECT Statement

Description

If you use the SELECT query in the INSERT statement, you can insert query results obtained from at least one table. The SELECT statement can be used in place of the VALUES keyword, or be included as a subquery in the column value list next to VALUES. If you specify the SELECT statement in place of the VALUES keyword, you can insert multiple query result records into the column of the table at once. However, there should be only one query result record if the SELECT statement is specified in the column value list.

In this way, you can extract data from another table that satisfies a certain retrieval condition, and insert it into the target table by combining the SELECT statement with the INSERT statement.

Syntax

INSERT [INTO] table_name [(column_name, ...)]

    SELECT...

    [ON DUPLICATE KEY UPDATE column_name = expr, ... ]

Example

--creating an empty table which schema replicated from a_tbl1

CREATE TABLE a_tbl2 LIKE a_tbl1;

 

--inserting multiple rows from SELECT query results

INSERT INTO a_tbl2 SELECT * FROM a_tbl1 WHERE id IS NOT NULL;

 

--inserting column value with SELECT subquery specified in the value list

INSERT INTO a_tbl2 VALUES(8, SELECT name FROM a_tbl1 WHERE name <'bbb', DEFAULT);

 

SELECT * FROM a_tbl2;

           id  name                  phone

=========================================================

            1  'aaa'                 '000-0000'

            2  'bbb'                 '000-0000'

            3  'ccc'                 '333-3333'

            4  NULL                  '000-0000'

            5  NULL                  '000-0000'

            6  'eee'                 '000-0000'

            7  NULL                  '777-7777'

            8  'aaa'                 '000-0000'