Skip to main content

INSERT

Insert records into a table, either from values provided in the SQL command or from rows returned by a SELECT statement.

If the table does not exist or the schema does not match, the INSERT operation fails.

Insert syntax 
INSERT INTO <table_name>
  [ ( <column_name1> [, <column_name2> ...]) ]
  { <select_statement>
    | VALUES (value [, value ...]) [, (value [, value ...]) ...] }

Parameters​

<table_name> String

The name of the table that you want to insert data into.

( <column_name1> [, <column_name2> ...] ) String   Optional

The name of the column or columns that you want to insert data into.

  • If column names are specified, data is inserted in the given column order, and columns not listed are populated as NULL.
  • If column names are not specified, data is inserted in the column order of the table.
  • Make sure the columns adhere to NOT NULL constraints specified in the table definition, or else the INSERT command fails with an error message.

<select_statement> String   Optional

The query that you want to use to insert into the current table data from another table.

VALUES (value [, value ...]) [, (value [, value ...]) ...] String   Optional

The value or values that you want to insert, if you specified the names of one or more columns. You can insert one or more sets of values, each set corresponding to a record to insert into the table.

Examples​

Inserts three records 
INSERT INTO myTable VALUES
    (21, 'Ruth Asawa', 'American, 1926–2013', 'American', 'Female', 1926, 2013, 'Q7382874', '500077806'),
    (38, 'Magdalena Abakanowicz', 'Polish, 1930–2017', 'Polish', 'Female', 1930, 2017, 'Q158080', '500084577'),
    (56, 'Luis Alberto Acuña', 'Colombian, 1904–1994', 'Colombian', 'Male', 1904, 1994, null, null)
Inserts three records at a table 
INSERT INTO myTable
  VALUES
    (21, 'Ruth Asawa', 'American, 1926–2013', 'American', 'Female', 1926, 2013, 'Q7382874', '500077806'),
    (38, 'Magdalena Abakanowicz', 'Polish, 1930–2017', 'Polish', 'Female', 1930, 2017, 'Q158080', '500084577'),
    (56, 'Luis Alberto Acuña', 'Colombian, 1904–1994', 'Colombian', 'Male', 1904, 1994, null, null)
Inserts values for a struct table 
INSERT INTO struct_type VALUES (convert_from('{ x: "hi" }', 'json'))

Limitations​

Column Position Mapping Requirement​

Dremio maps INSERT columns by position. Previously, Dremio fell back to name-based matching between the source and target table instead of using positional order, which could cause data to be inserted into unexpected columns. For example, take the below queries:

Create tables and insert data with mismatched column order 
CREATE TABLE employees_final (id INT, first_name VARCHAR, last_name VARCHAR, address_data MAP<VARCHAR, VARCHAR>, last_name_corrected VARCHAR);
CREATE TABLE employees_staging (id INT, address_data MAP<VARCHAR, VARCHAR>, last_name_corrected VARCHAR, first_name VARCHAR);
INSERT INTO employees_staging VALUES (1, NULL, 'Smith', 'Jason');

-- This query previously succeeded via name-based column matching; it now fails with a type mismatch error under positional matching.
INSERT INTO employees_final SELECT id, address_data, last_name_corrected, first_name FROM employees_staging;

Previously, Dremio matched columns by name instead of position, so this query wrote the value of employees_staging.last_name_corrected into employees_final.last_name_corrected instead of the intended employees_final.last_name field. With positional matching, the same query fails with a type mismatch error instead, since address_data (a MAP) no longer aligns by name with first_name (a VARCHAR).

Rewrite the query so that the columns in the SELECT portion of the INSERT match the order of columns in the target employees_final table:

Insert with columns matched by position 
INSERT INTO employees_final SELECT id, first_name, last_name_corrected, address_data FROM employees_staging;

This leaves employees_final.last_name_corrected as NULL, since the SELECT list does not provide a value for it.