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 | 



Overview

Description

You can insert a new record into a table in a database by using the INSERT statement. CUBRID supports INSERT...VALUES, INSERT...SET and INSERT...SELECT statements.

INSERT...VALUES and INSERT...SET statements are used to insert a new record based on the value that is explicitly specified while the INSERT...SELECT statement is used to insert query result records obtained from different tables. Use the INSERT VALUES or INSERT...SELECT statement to insert multiple rows by using the single INSERT statement. 

Syntax

<INSERT … VALUES statement>

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

    {VALUES | VALUE}({expr | DEFAULT}, ...)[,({expr | DEFAULT}, ...),...]

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

INSERT [INTO] table_name DEFAULT [ VALUES ]

INSERT [INTO] table_name VALUES()

 

<INSERT … SET statement>

INSERT [INTO] table_name

    SET column_name = {expr | DEFAULT}[, column_name = {expr | DEFAULT},...]

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

 

<INSERT … SELECT statement>

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

    SELECT...

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

  • table_name : Specify the name of the target table into which you want to insert a new record.
  • column_name : Specify the name of the column into which you want to insert the value. If you omit to specify the column name, it is considered that all columns defined in the table have been specified. Therefore, you must specify the values for all columns next to the VALUES keyword. If you do not specify all the columns defined in the table, a DEFAULT value is assigned to the non-specified columns; if the DEFAULT value is not defined, a NULL value is assigned.
  • expr | DEFAULT : Specify values that correspond to the columns next to the VALUES keyword. Expressions or the DEFAULT keyword can be specified as a value. At this time, the order and number of the specified column list must correspond to the column value list. The column value list for a single record is described in parentheses.
  • DEFAULT : You can use the DEFAULT keyword to specify a default value as the column value. If you specify DEFAULT in the column value list next to the VALUES keyword, a default value column is stored for the given column: if you specify DEFAULT before the VALUES keyword, default values are stored for all columns in the table. NULL is stored for the column whose default value has not been defined.
  • ON DUPLICATE KEY UPDATE : In case constraints are violated because a duplicated value for a column where PRIMARY KEY or UNIQUE attribute is defined is inserted, the value that makes constraints violated is changed into a specific value by performing the action specified in the ON DUPLICATE KEY UPDATE statement.
Example

CREATE TABLE a_tbl1(

id INT UNIQUE,

name VARCHAR,

phone VARCHAR DEFAULT '000-0000');

 

--insert default values with DEFAULT keyword before VALUES

INSERT INTO a_tbl1 DEFAULT VALUES;

 

--insert multiple rows

INSERT INTO a_tbl1 VALUES (1,'aaa', DEFAULT),(2,'bbb', DEFAULT);

 

--insert a single row specifying column values for all

INSERT INTO a_tbl1 VALUES (3,'ccc', '333-3333');

 

--insert two rows specifying column values for only

INSERT INTO a_tbl1(id) VALUES (4), (5);

 

--insert a single row with SET clauses

INSERT INTO a_tbl1 SET id=6, name='eee';

INSERT INTO a_tbl1 SET id=7, phone='777-7777';

 

SELECT * FROM a_tbl1;

           id  name                  phone

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

         NULL  NULL                  '000-0000'

            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'