SQL
Insert Column Order
It's possible to provide an optional insert column order, this can either be `BY POSITION` (the default) or `BY NAME`.
Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or `NULL` if there is none.  
If the expression for any column is not of the correct data type, automatic type conversion will be attempted.  
#### `INSERT INTO ... [BY POSITION]` {#docs:sql:statements:insert::insert-into--by-position}  
The order that values are inserted into the columns of the table is determined by the order that the columns were declared in.
That is, the values supplied by the `VALUES` clause or query are associated with the column list left-to-right.
This is the default option, that can be explicitly specified using the `BY POSITION` option.
For example:  
```sql
CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl
VALUES (5, 42);
```  
Specifying `BY POSITION` is optional and is equivalent to the default behavior:  
```sql
INSERT INTO tbl
BY POSITION
VALUES (5, 42);
```  
To use a different order, column names can be provided as part of the target, for example:  
```sql
CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl (b, a)
VALUES (5, 42);
```  
Adding `BY POSITION` results in the same behavior:  
```sql
INSERT INTO tbl
BY POSITION (b, a)
VALUES (5, 42);
```  
This will insert `5` into `b` and `42` into `a`.  
#### `INSERT INTO ... BY NAME` {#docs:sql:statements:insert::insert-into--by-name}  
Using the `BY NAME` modifier, the names of the column list of the `SELECT` statement are matched against the column names of the table to determine the order that values should be inserted into the table. This allows inserting even in cases when the order of the columns in the table differs from the order of the values in the `SELECT` statement or certain columns are missing.  
For example:  
```sql
CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a);
INSERT INTO tbl BY NAME (SELECT 22 AS b);
SELECT * FROM tbl;
```  
|  a   | b  |
|-----:|---:|
| 32   | 42 |
| NULL | 22 |  
It's important to note that when using `INSERT INTO ... BY NAME`, the column names specified in the `SELECT` statement must match the column names in the table. If a column name is misspelled or does not exist in the table, an error will occur. Columns that are missing from the `SELECT` statement will be filled with the default value.