DML (Data Manipulation Language)

Data Manipulation Language (DML) statements are used to insert, update, and delete data in tables. Spice supports DML operations on write-capable data connectors configured with access: read_write. An accelerated dataset over a source that accepts only reads can also take DML with acceleration.write_mode: acceleration, which keeps the writes in the acceleration.

INSERT

Insert new rows into a table.

Syntax

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

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

+-------+
| count |
+-------+
| 1     |
+-------+

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');

+-------+
| count |
+-------+
| 3     |
+-------+

Insert All Columns (Optional Column List)

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

Insert from Query

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';