# DML (Data Manipulation Language)

Data Manipulation Language (DML) statements for inserting and modifying data in Spice.

Data Manipulation Language (DML) statements are used to insert, update, and delete data in tables. Spice supports DML operations on [write-capable data connectors](https://spiceai.org/docs/tags/write) configured with `access: read_write`. An accelerated dataset over a source that accepts only reads can also take DML with [`acceleration.write_mode: acceleration`](https://spiceai.org/docs/reference/spicepod/datasets#accelerationwrite_mode), which keeps the writes in the acceleration.

> **Warning**
>
> **Supported Operations**
>
> Spice supports `INSERT` for write-capable connectors and `MERGE INTO` for [Spice Cayenne](https://spiceai.org/docs/components/data-accelerators/cayenne) catalog tables. Whether a dataset takes standalone `UPDATE` and `DELETE` depends on its connector or accelerator; each component's page lists the statements it supports. A dataset with `write_mode: acceleration` takes the statements its accelerator engine supports; on Spice Cayenne, that includes `UPDATE` and `DELETE`.

> **Note**
>
> Spice is built on [Apache DataFusion](https://datafusion.apache.org/) and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects.

## INSERT

Insert new rows into a table.

### Syntax

```sql
INSERT INTO table_name [ ( column_name [, ...] ) ]
{ VALUES ( expression [, ...] ) [, ...] | query }
```

### Parameters

- **`table_name`**: The name of the target table
- **`column_name`**: Optional list of column names to insert into. If omitted, values must be provided for all columns in table order
- **`expression`**: Values to insert into the corresponding columns
- **`query`**: A SELECT statement to insert results from another table or query

### Examples

#### Insert Single or Multiple Rows

```sql
INSERT INTO customers (id, name, email)
VALUES (1, 'Alice Smith', 'alice@example.com');
```

```text
+-------+
| count |
+-------+
| 1     |
+-------+
```

```sql
INSERT INTO customers (id, name, email)
VALUES
  (2, 'Bob Johnson', 'bob@example.com'),
  (3, 'Carol Wilson', 'carol@example.com'),
  (4, 'David Brown', 'david@example.com');
```

```text
+-------+
| count |
+-------+
| 3     |
+-------+
```

#### Insert All Columns (Optional Column List)

```sql
INSERT INTO products
VALUES (101, 'Laptop', 999.99, 'Electronics');
```

#### Insert from Query

```sql
INSERT INTO archive_orders (order_id, customer_id, total, order_date)
SELECT order_id, customer_id, total, order_date
FROM orders
WHERE order_date < '2024-01-01';
```