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.

info

Spice is built on Apache DataFusion and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects.

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', '[email protected]');

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

INSERT INTO customers (id, name, email)
VALUES 
  (2, 'Bob Johnson', '[email protected]'),
  (3, 'Carol Wilson', '[email protected]'),
  (4, 'David Brown', '[email protected]');

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