*** DRAFT ***
The INSERT statement comes in three basic forms.
INSERT INTO table VALUES(...);
The first form (with the "VALUES" keyword) creates one or more new rows in an existing table. If the column-name list after table-name is omitted then the number of values inserted into each row must be the same as the number of columns in the table. In this case the result of evaluating the left-most expression from each term of the VALUES list is inserted into the left-most column of each new row, and so forth for each subsequent expression. If a column-name list is specified, then the number of values in each term of the VALUE list must match the number of specified columns. Each of the named columns of the new row is populated with the results of evaluating the corresponding VALUES expression. Table columns that do not appear in the column list are populated with the default column value (specified as part of the CREATE TABLE statement), or with NULL if no default value is specified.
INSERT INTO table SELECT ...;
The second form of the INSERT statement contains a SELECT statement instead of a VALUES clause. A new entry is inserted into the table for each row of data returned by executing the SELECT statement. If a column-list is specified, the number of columns in the result of the SELECT must be the same as the number of items in the column-list. Otherwise, if no column-list is specified, the number of columns in the result of the SELECT must be the same as the number of columns in the table. Any SELECT statement, including compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may be used in an INSERT statement of this form.
To avoid a parsing ambiguity, the SELECT statement should always contain a WHERE clause, even if that clause is simply "WHERE true", if the upsert-clause is present. Without the WHERE clause, the parser does not know if the token "ON" is part of a join constraint on the SELECT, or the beginning of the upsert-clause.
INSERT INTO table DEFAULT VALUES;
The third form of an INSERT statement is with DEFAULT VALUES. The INSERT ... DEFAULT VALUES statement inserts a single new row into the named table. Each column of the new row is populated with its default value, or with a NULL if no default value is specified as part of the column definition in the CREATE TABLE statement. The upsert-clause is not supported after DEFAULT VALUES.
The initial "INSERT" keyword can be replaced by "REPLACE" or "INSERT OR action" to specify an alternative constraint conflict resolution algorithm to use during that one INSERT command. For compatibility with MySQL, the parser allows the use of the single keyword REPLACE as an alias for "INSERT OR REPLACE".
The optional "schema-name." prefix on the table-name is supported for top-level INSERT statements only. The table name must be unqualified for INSERT statements that occur within CREATE TRIGGER statements. Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for top-level INSERT statements only and not for INSERT statements within triggers.
The optional "AS alias" phrase provides an alternative name for the table into which content is being inserted. The alias name can be used within WHERE and SET clauses of the UPSERT. If there is no upsert-clause, then the alias is pointless, but also harmless.
See the separate UPSERT documentation for the additional trailing syntax that can cause an INSERT to behave as an UPDATE if the INSERT would otherwise violate a uniqueness constraint. The upsert clause is not allowed on an "INSERT ... DEFAULT VALUES".
Consider an INSERT of the following form:
INSERT INTO dest SELECT * FROM src;
The default algorithm used for this kind of INSERT is to read each row from src table, break that row into its individual columns, reassemble those individual values back into a new row and insert the new row into the dest table. The following diagram illustrates:
When the on-disk binary format of the row in in src table is identical to the format of the row in the destination table, then the disassembly and reassembly of the row can be skipped, resulting in a simpler and faster algorithm:
This is called the "xfer-optimization". From the algorithm diagrams above, you can perhaps understand why the xfer-optimization helps the content transfer to run faster.
The xfer-optimization only works when the on-disk binary format of the row is bit-for-bit the same in both the src and dest tables. The exact conditions when this is true are nuanced, but generally speaking, the schema of the src and dest tables need to be the same, or nearly so and the INSERT needs to be in the form shown above, with a single source table, no WHERE clause, and no WITH clause or other accoutrements.
The xfer optimization works even if the source and destination tables contain generated columns. Suppose the tables look like this:
CREATE TABLE src(a INT, b INT, sum INT AS (a+b), diff INT AS (a-b)); CREATE TABLE dest(x INT, y INT, sum INT AS (x+y), diff INT AS (x-y));
Then SQLite still allows you to transfer information from src to dest using a statement like:
INSERT INTO dest SELECT * FROM src;
Technically, the statement above is incorrect SQL. The SELECT state returns four columns, but the INSERT is expecting only two columns. It should generate an error. However, many early versions of SQLite allow it. By the time the bug was discovered, it had been in such wide distribution, it was decided not to fix it lest it break existing code. The bug is harmless, and in fact can be quite useful. The VACUUM command uses it interally, both to simplify its implementation and to improve performance. So we call this behavior a feature. The only problem this feature might cause is that the INSERT statement will stop working if you ever port your application to a different SQL implementation.
Note that if you add addition details that prevent the xfer-optimization from working (such as adding a WHERE clause) then the statement above will fail with an error, similar to:
Parse error: table dest has 2 columns but 4 values were supplied
This page was last updated on 2026-05-14 15:13:28Z
*** DRAFT ***