INSERT INTO
Learning Focus
Use this lesson to understand INSERT INTO — adding data to DuckDB tables from values or query results.
Basic Syntax
-- Single row
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
-- Multiple rows
INSERT INTO table_name (col1, col2) VALUES
(val1a, val2a),
(val1b, val2b);
-- From a query
INSERT INTO table_name SELECT col1, col2 FROM other_table;
Examples
Example 1: Insert a Single Row
INSERT INTO employees (id, name, department, salary, hire_date, active)
VALUES (6, 'Frank Lee', 'Engineering', 91000.00, '2024-02-01', true);
Example 2: Insert Multiple Rows
INSERT INTO employees VALUES
(7, 'Grace Kim', 'Marketing', 69000.00, '2023-07-15', true),
(8, 'Henry Park', 'Finance', 82000.00, '2022-12-01', true);
Example 3: Insert from SELECT
-- Copy active employees to an archive table
INSERT INTO employees_archive
SELECT * FROM employees WHERE active = false;
Example 4: Insert from CSV File (DuckDB-specific)
-- Insert all rows from a CSV file
INSERT INTO employees
SELECT * FROM read_csv_auto('new_hires.csv');
Example 5: CREATE TABLE AS SELECT (CTAS)
-- Create a new table from a query result
CREATE TABLE eng_employees AS
SELECT * FROM employees WHERE department = 'Engineering';
Example 6: INSERT OR REPLACE
-- Replace if primary key conflicts
INSERT OR REPLACE INTO employees
VALUES (1, 'Alice Updated', 'Engineering', 100000.00, '2020-03-15', true);
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Column count mismatch | Error: values must match columns | Always list target columns explicitly |
| Wrong data types | Type casting errors | Verify types with DESCRIBE employees |
| Forgetting PRIMARY KEY uniqueness | Constraint violation | Use INSERT OR REPLACE or INSERT OR IGNORE |
Quick Reference
INSERT INTO t (c1, c2) VALUES (v1, v2);
INSERT INTO t SELECT * FROM other;
INSERT INTO t SELECT * FROM read_csv_auto('file.csv');
CREATE TABLE new_t AS SELECT * FROM t WHERE condition;
INSERT OR REPLACE INTO t VALUES (...);
What's Next
- Next: UPDATE - Modify existing rows in a table.
- Module Overview - Return to this module index.