INSERT¶
Updates a table by inserting one or more rows into the table. The values inserted into each column in the table can be explicitly-specified or the results of a query.
- See also:
Syntax¶
To replace selected rows, use OVERWRITE with DELETING WHERE:
Required parameters¶
target_tableSpecifies the target table into which to insert rows.
VALUES ( value | DEFAULT | NULL [ , ... ] ) [ , ( ... ) ]Specifies one or more values to insert into the corresponding columns in the target table.
In a
VALUESclause, you can specify the following:value: Inserts the explicitly-specified value. The value can be a literal or an expression that evaluates to a single value.DEFAULT: Inserts the default value for the corresponding column in the target table.NULL: Inserts aNULLvalue.
Each value in the clause must be separated by a comma.
You can insert multiple rows by specifying additional sets of values in the clause. For more information, see the Usage notes and the Examples.
querySpecify a query statement that returns values to be inserted into the corresponding columns. This allows you to insert rows into a target table from one or more source tables.
Optional parameters¶
OVERWRITESpecifies that existing rows are removed before new rows are inserted. Without
DELETING WHERE, all existing rows are removed. WithDELETING WHERE, only rows for which the predicate evaluates toTRUEare removed. Specifying this option doesn’t change the access control privileges granted on the table.INSERT statements with
OVERWRITEcan be processed within the scope of the current transaction, which avoids DDL statements that commit a transaction, such as:Default: No value (existing rows aren’t removed before performing the inserts).
AS target_aliasSpecifies an alias for the target table in a conditional overwrite statement. Use this alias to qualify target columns in
DELETING WHERE. TheASkeyword is required for this alias.USING ( using_query ) [ AS ] delta_aliasDefines a relation that subqueries in
DELETING WHEREand the insertion query can both reference. The relation must have an alias;ASbefore this alias is optional. TheUSINGquery can’t reference the target alias.The relation isn’t an implicit row source in the deletion predicate. For example, use
t.id IN (SELECT id FROM delta), rather thant.id = delta.id.DELETING WHERE ( predicate )Specifies which existing target rows to remove. The predicate must be a Boolean expression enclosed in parentheses. Rows for which the predicate evaluates to
FALSEorNULLremain in the table.Deletion and insertion are independent: all rows returned by the insertion query are inserted, even if they don’t satisfy the deletion predicate. Matching target rows are removed even when the insertion query returns no rows. The predicate doesn’t apply to newly inserted rows.
This clause requires
OVERWRITE. If you specify a target column list, place it afterDELETING WHERE.( target_col_name [ , ... ] )Specifies one or more columns in the target table into which the corresponding values are inserted. The number of target columns specified must match the number of specified values or columns (if the values are the results of a query) in the
VALUESclause.Default: No value (all the columns in the target table are updated).
Usage notes¶
-
Using a single INSERT command, you can insert multiple rows into a table by specifying additional sets of values separated by commas in the VALUES clause.
For example, the following clause would insert 3 rows in a 3-column table, with values
1,2, and3in the first two rows and values2,3, and4in the third row: -
To use
OVERWRITEonINSERT, you must use a role that has bothINSERTandDELETEprivileges on the target table.DELETING WHEREdoesn’t require theTRUNCATEprivilege. -
DELETING WHEREis supported only for single-targetINSERT OVERWRITE. It isn’t supported for plainINSERT INTOor multi-table INSERT. -
Conditional overwrite supports permanent, temporary, and transient tables, and writable Iceberg tables. It doesn’t support versioned targets, such as an Iceberg branch, or other special table types such as hybrid, interactive, and event tables.
-
To use conditional overwrite in a custom incremental dynamic table, specify
SELFas the target insideREFRESH USING. Conditional overwrite isn’t supported as a direct statement against a dynamic table. -
Conditional overwrite preserves duplicates in retained rows and inserted rows. It doesn’t deduplicate replacement rows. Deletion and insertion form one transactional operation: if insertion fails, the deletion isn’t committed.
-
Some types of expressions can’t be specified in the VALUES clause, including the following expressions:
-
Subqueries
For example:
-
Values of the semi-structured or structured data type.
For example:
-
For example:
-
For example:
As an alternative to the VALUES clause, specify the expression in a
queryclause. For example, you can replace the following expression:with this expression:
-
-
The VALUES clause is limited to 200,000 rows. This limit applies to a single INSERT INTO … VALUES statement and a single INSERT INTO … SELECT … FROM VALUES statement. Consider using the COPY INTO <table> command to perform a bulk data load. For more information about using the VALUES clause in a SELECT statement, see VALUES.
-
For information about inserting data into hybrid tables, see Loading data.
-
If error logging is turned on for the target table, a supported error, such as a NOT NULL or CHECK constraint violation, doesn’t cause the statement to fail. Instead, the statement records the error in the table’s error table and continues with the other rows. For more information, see Error logging for DML statements.
Examples¶
The following examples use the INSERT command.
Single row insert using a query¶
Convert three string values to dates or timestamps and insert them into a single row in the mytable table:
Similar to previous example, but specify to update only the first and third columns in the table:
Multi-row insert using explicitly-specified values¶
Create the employees table and insert four rows of data into it by providing sets of values in a
comma-separated list in the VALUES clause:
In multi-row inserts, make sure that the data types of the inserted values are consistent across the rows because the data type of the first row is used as a guide. Create a table and insert two rows:
The first insert works as expected:
The second insert fails because the data type of the value in the second row ('d') is a string, which is
different from the numeric data type of the value in the first row (3). The insert fails even though both values
can be coerced to VARCHAR, which is the data type of the column in
the table. The insert fails even though the data type of the value 'd' is the same as the data type of column v:
When the data types are consistent across the rows, the insert succeeds, and both numeric values are coerced to the VARCHAR data type:
Multi-row insert using query¶
Insert multiple rows of data from the contractors table into the employees table:
- Select only those rows where the
worknumcolumn contains area code650. - Insert a NULL value in the
citycolumn.
Insert multiple rows of data from the contractors table into the employees table using a common table expression:
Insert columns from two tables (emp_addr, emp_ph) into a third table (emp) using an INNER JOIN on the id
column in the source tables:
Multi-row insert for JSON data¶
Insert two JSON objects into a VARIANT column in a table:
Insert using OVERWRITE¶
This example uses INSERT with OVERWRITE to rebuild the sf_employees table from employees after new records were added
to the employees table.
Here is the initial data for both tables:
This statement inserts rows into the sf_employees table using the OVERWRITE clause:
Because the INSERT used the OVERWRITE clause, the old rows from sf_employees are gone:
Replace selected rows using DELETING WHERE¶
This example removes all existing rows for a selected key and inserts one replacement. Rows with other keys remain.
Both old rows with id = 1 are removed:
A replacement query that returns no rows still removes the matching target rows:
Only the unmatched row remains:
For a refresh that replaces multiple dimension combinations for each changed fact, see Stream-static join with multiple dimension matches.
Write to a v3 Apache Iceberg™ table¶
Note
For more information about other Iceberg v3 features that Snowflake supports, see Apache Iceberg™ tables: Support for Apache Iceberg™ v3.
The following example inserts a row into an Apache Iceberg™ table that conforms to v3 of the Apache Iceberg™ table specification: