<< Home

Chapter 8: Inserting Data

Adding rows to tables is one of the most common database operations. The INSERT family of statements supports single and multiple row inserts, copying data from other tables, and creating new tables directly from query results. This chapter covers the core syntax and useful patterns across engines.

⚠️ Always define explicit column lists in your INSERT statements. It prevents silent mismatches when the table structure changes.

INSERT INTO syntax and multi-row inserts

The standard form specifies the target table, a list of columns, and the values to insert. Each value list corresponds to one row.

INSERT INTO employees (first_name, last_name, role, salary)
VALUES ('Alice', 'Green', 'Manager', 90000);

To insert multiple rows in one statement, provide additional value sets separated by commas. This is efficient and supported by all major engines.

INSERT INTO employees (first_name, last_name, role, salary)
VALUES 
  ('Bob', 'White', 'Engineer', 75000),
  ('Carla', 'Smith', 'Designer', 70000),
  ('David', 'Brown', 'Engineer', 77000);
💡 Multi-row inserts greatly reduce transaction overhead when adding many rows. Use this pattern for batch loads rather than looping individual inserts.

Column omission and defaults

If a column has a default value or allows nulls, you can omit it from the insert list. The database uses the default expression or sets it to NULL.

-- 'created_at' defaults to CURRENT_TIMESTAMP
INSERT INTO accounts (username, email)
VALUES ('jdoe', 'jdoe@example.com');
⚠️ Relying on implicit column order is risky. Always specify column names in both INSERT INTO and SELECT clauses.

Engine-specific bulk patterns

-- PostgreSQL bulk load (server-side)
COPY employees (first_name, last_name, role, salary)
FROM '/tmp/employees.csv'
WITH (FORMAT csv, HEADER true);

-- MySQL
LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
💡 Use database-native bulk loaders for millions of rows. They bypass per-row overhead and can commit in larger chunks.

Inserting from subqueries or SELECT … INTO

You can populate a table using data from another query. This pattern is common for data transformations, archiving, or building summary tables.

Using INSERT INTO ... SELECT

The INSERT INTO ... SELECT form inserts the results of a query into an existing table.

-- Copy all engineers into a new department
INSERT INTO engineers (first_name, last_name, salary)
SELECT first_name, last_name, salary
FROM employees
WHERE role = 'Engineer';

The columns in the SELECT must align in number and type with those in the target list.

⚠️ Some engines implicitly commit after large inserts from subqueries. Wrap statements in transactions if you need atomicity across multiple steps.

Transforming during insert

-- Apply transformations as you insert
INSERT INTO archive_orders (order_id, customer_id, total, archived_at)
SELECT id, customer_id, total, CURRENT_TIMESTAMP
FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '1 year';
💡 Combining filters and transformations during insert reduces I/O. You move only the necessary data rather than inserting everything and cleaning it later.

Creating new tables with SELECT ... INTO

Some engines allow you to create a new table directly from a query result. The table is created with column names and types inferred from the query.

-- SQL Server
SELECT customer_id, COUNT(*) AS order_count
INTO customer_summary
FROM orders
GROUP BY customer_id;

-- PostgreSQL and MySQL
CREATE TABLE customer_summary AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- SQLite
CREATE TABLE customer_summary AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
⚠️ SELECT ... INTO creates a physical table only once. If you run it again with the same table name, you get an error unless you drop or truncate first.

Combining inserts with conflict handling

When inserting from a subquery, duplicates can occur. Each engine offers options to handle them gracefully.

-- PostgreSQL: upsert pattern
INSERT INTO users (email, name)
SELECT email, name FROM staging_users
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name;

-- MySQL
INSERT INTO users (email, name)
SELECT email, name FROM staging_users
ON DUPLICATE KEY UPDATE
  name = VALUES(name);

-- SQL Server (using MERGE)
MERGE INTO users AS target
USING staging_users AS source
ON target.email = source.email
WHEN MATCHED THEN
  UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
  INSERT (email, name) VALUES (source.email, source.name);
💡 For large merges, consider staging into a temporary table first. It lets you validate data and control transaction scope before affecting production tables.

Understanding how to insert efficiently—whether single, multi-row, or from queries—sets the foundation for loading, migrating, and transforming data safely and quickly.