Skip to main content

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