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.

Supported Operations

Spice supports INSERT for write-capable connectors and MERGE INTO for Spice Cayenne catalog tables. UPDATE and DELETE statements are not yet supported as standalone operations. For data modifications, use MERGE INTO or the source database directly.

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