INSERT

Inserts one or more rows into a table, either explicitly specified or as the result of a query.

Syntax

INSERT INTO target_table [(target_col_name[, ... ])]
       {
        VALUES ({value | DEFAULT | NULL} [, ...]) [, (...)]  
        | query
        }

Parameters

target_table: The target table into which the rows will be inserted.

---

VALUES ({expr | DEFAULT | NULL} [, ...]) [, (...)]: Values to be inserted into the target_table.

expr: An explicitly provided value (a literal or expression).

DEFAULT: Inserts the default value of the corresponding column.

NULL: Inserts a NULL value.

Individual values are comma-separated. Groups of values representing individual rows must be enclosed in parentheses and separated by commas. The data types of the inserted values must be consistent across all rows. The data type of the first row determines the expected type. A mismatch will result in an error.

Example

VALUES (1, 2, 3),
       (6, 7, 9),
       (10, 23, 12)

Inserts 3 rows.

---

query: A query that returns values to be inserted into the corresponding columns. This allows inserting values from multiple tables into one.

---

target_col_name: Names of the columns in the target_table into which the values will be inserted. The number of columns must match the number of values in the VALUES clause or the number of columns in the result set of the query. If target_col_name is omitted, the values will be inserted into the table columns in positional order.

Important

Not all expressions in the VALUES clause are supported. It is recommended to use subqueries as an alternative.

Example 1 | INSERT with SELECT clause

create table  mkw_doku.my_table1 (ord string, col1 integer);                
insert into   mkw_doku.my_table1 (select 'a' as ord, 10 as col1);   
select * from mkw_doku.my_table1;

+-----+------+
| ORD | COL1 |
+-----+------+
| a   | 10   |
+-----+------+

Example 2 | INSERT with SELECT clause and explicit columns

Only one column is populated.

create table   mkw_doku.my_table1 (ord string, col1 integer);
insert into    mkw_doku.my_table1 (ord) (select 'f' as ord);    
select * from  mkw_doku.my_table1;

+-----+------+
| ORD | COL1 |
+-----+------+
| f   | NULL |
+-----+------+

Example 3 | INSERT with VALUES clause

create table mkw_doku.my_table1 (ord string, col1 integer);
insert into mkw_doku.my_table1 (ord,col1)                        
       values ('b',20);                                 
select * from mkw_doku.my_table1;

+-----+------+
| ORD | COL1 |
+-----+------+
| b   | 20   |
+-----+------+

Example 4 | INSERT ... SELECT from another table

create table  mkw_doku.my_table1 (ord string, col1 integer);
create table  mkw_doku.my_table2 (ord_tab2 string, col2_tab2 integer);

insert into mkw_doku.my_table1 
                      (select 'a' as ord, 10 as col1
                       union all 
                       select 'b' as ord,  20 as col1
                       union all
                       select 'c' as ord,  30 as col1);

insert into mkw_doku.my_table2 
                      (select 'd',  40
                       union all
                       select 'f',  null);

insert into mkw_doku.my_table1(ord, col1)                   
  (select
    ord_tab2, col2_tab2 from mkw_doku.my_table2);           
    
select * from mkw_doku.my_table1 order by ord;

+-----+------+
| ORD | COL1 |
+-----+------+
| a   | 10   |
| b   | 20   |
| c   | 30   |
| d   | 40   |
| f   | NULL |
+-----+------+

Example 5 | INSERT with Common Table Expressions (CTEs)

create table  mkw_doku.my_table1 (ord string, col1 integer);
create table  mkw_doku.my_table2 (ord_tab2 string, col2_tab2 integer);

insert into mkw_doku.my_table2 
                      (select 'd',  40 
                       union all
                       select 'f',  null);
                       
insert into mkw_doku.my_table1 (ord,col1)                                            
  with my_cte as (select ord_tab2 as ord, col2_tab2 as col1 from mkw_doku.my_table2) 
      select ord, col1                                                               
            from my_cte;                                                             
        
select * from mkw_doku.my_table1;

+-----+------+
| ORD | COL1 |
+-----+------+
| d   | 40   |
| f   | NULL |
+-----+------+