# INSERT INTO

INSERT adds rows to a table. Values can come from literal expressions, a SELECT, column defaults, a registered input format or files supplied by the native client.

An explicit column list maps input values to the named columns. Omitted columns receive their SQL DEFAULT expression or the applicable default behavior. Declared types and constraints validate every inserted value.

## Syntax

```text
INSERT [OR ABORT | OR FAIL | OR IGNORE | OR REPLACE | OR ROLLBACK]
INTO table [(column, ...)]
VALUES (value, ...) [, ...]
[ON CONFLICT ...]
[RETURNING expression, ...]

INSERT INTO table [(column, ...)] SELECT ...
INSERT INTO table DEFAULT VALUES

INSERT INTO table [(column, ...)] [SETTINGS ...] FORMAT name
<data>

INSERT INTO table [(column, ...)]
FROM INFILE 'path' [COMPRESSION 'type'] [SETTINGS ...] [FORMAT name]
```

## Examples

### Inserting selected columns

```sql
CREATE TABLE products
(
    id INTEGER PRIMARY KEY,
    name String NOT NULL,
    price Decimal(18, 2) DEFAULT '0.00'
);
INSERT INTO products(id, name) VALUES (1, 'Notebook'), (2, 'Pen');
```

```sql
SELECT id, name, price
FROM products
ORDER BY id
FORMAT csv;
```

```text
1,"Notebook",0.00
2,"Pen",0.00
```

### Inserting a query result

```sql
CREATE TABLE archived_products AS SELECT * FROM products WHERE 0;
INSERT INTO archived_products
SELECT * FROM products WHERE id = 1;
```

```sql
SELECT id, name
FROM archived_products
FORMAT csv;
```

```text
1,"Notebook"
```

### Updating a conflicting row

`excluded` exposes the proposed inserted row inside `DO UPDATE`:

```sql
INSERT INTO products(id, name, price)
VALUES (1, 'Notebook', '12.50')
ON CONFLICT(id) DO UPDATE SET price = excluded.price
RETURNING id, price
FORMAT csv;
```

```text
1,12.50
```

`ON CONFLICT ... DO NOTHING` skips a conflicting row. Multiple conflict clauses and the listed `OR` conflict policies are supported.

## Formatted input

`INSERT ... FORMAT` decodes input rows with a registered format. Place `SETTINGS` before the format name: the remaining request bytes are data, including any semicolons. Send later SQL in a separate request.

```sql
CREATE TABLE imported_orders(id UInt32, amount UInt32);
```

```sql
INSERT INTO imported_orders FORMAT csv
1,100
2,60
```

```sql
SELECT id, amount
FROM imported_orders
ORDER BY id
FORMAT csv;
```

```text
1,100
2,60
```

Formatted input is the last statement in its request and does not accept `RETURNING` or `ON CONFLICT` clauses. The `OR` conflict policies still apply. Omitted format fields can use implicit type defaults according to the selected format and settings.

`FROM INFILE` reads files on the native client's machine and supplies their decoded rows to the server. See [bulk inserts](https://coordinatordb.com/docs/concepts/features/operations/insert/bulkinserts) and the [format reference](https://coordinatordb.com/docs/reference/formats) for supported sources and options.