INSERT: Single Row, Multi Row and INSERT SELECT

Concept

INSERT adds rows. The column list you write is a contract: it says which columns you are supplying, and every other column takes its DEFAULT, its auto increment value, or NULL.

Syntax

-- Named columns: the only form worth writing
INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

-- Several rows in one statement
INSERT INTO table_name (col1, col2)
VALUES (v1, v2),
       (v3, v4),
       (v5, v6);

-- Rows produced by a query
INSERT INTO table_name (col1, col2)
SELECT other_col1, other_col2
FROM   source_table
WHERE  condition;

Example

-- One row, columns named
INSERT INTO departments (id, name, location)
VALUES (50, 'Support', 'Chennai');

-- Several rows in a single round trip
INSERT INTO employees (id, first_name, last_name, email, dept_id, hire_date, salary)
VALUES (9,  'Sara',  'Khan',   'sara@example.com',  50, '2024-06-03', 67000.00),
       (10, 'Vivek', 'Menon',  'vivek@example.com', 50, '2024-06-17', 72500.00);

-- Rows built from a query: archive last year's shipped orders
INSERT INTO orders_archive (id, customer_id, order_date, status, total)
SELECT id, customer_id, order_date, status, total
FROM   orders
WHERE  status = 'shipped'
  AND  order_date < '2024-01-01';

Explanation

The multi row form is not just tidier - it is one statement, one round trip and one transaction. Inserting 5,000 rows as 5,000 separate statements can be an order of magnitude slower than batching them.

INSERT ... SELECT never touches the application: the rows are copied inside the database. Note that status was left out of the employee inserts, so both new rows get the column default 'active'.

Handling duplicate keys

-- MySQL / MariaDB
INSERT INTO departments (id, name, location)
VALUES (50, 'Support', 'Chennai')
ON DUPLICATE KEY UPDATE location = VALUES(location);

-- PostgreSQL and SQLite
INSERT INTO departments (id, name, location)
VALUES (50, 'Support', 'Chennai')
ON CONFLICT (id) DO UPDATE SET location = EXCLUDED.location;

-- Standard SQL, supported by SQL Server and Oracle
MERGE INTO departments d
USING (SELECT 50 AS id, 'Support' AS name, 'Chennai' AS location) s
   ON d.id = s.id
 WHEN MATCHED THEN UPDATE SET d.location = s.location
 WHEN NOT MATCHED THEN INSERT (id, name, location) VALUES (s.id, s.name, s.location);

Important rules

• The value order must match the column list, not the table definition.
• Omitting a column means default, auto increment or NULL - in that order of preference. If the column is NOT NULL with no default, the insert fails.
• An INSERT that violates any constraint fails and inserts nothing from that row.
• In most engines a multi row INSERT is atomic: if one row fails, none of them are stored.
• Never quote numbers. '50' may work through implicit conversion, but it can silently defeat an index.

Common mistakes

• Writing INSERT INTO employees VALUES (...) with no column list. The day someone adds a column, every one of those statements breaks.
• Supplying a value for an auto increment column and creating a gap or a future collision.
• Inserting a child row before its parent exists, which the foreign key rejects.
• Looping in application code to insert rows one at a time when one statement would do.

Best practices

• Always list the columns explicitly.
• Batch inserts in groups of a few hundred to a few thousand rows.
• Let the database generate surrogate keys instead of computing MAX(id) + 1 in application code, which is a race condition waiting to happen.
• Use parameterised statements from application code, never string concatenation. See the note on SQL injection.

Practice

1. Insert three products into the sample products table in one statement.
2. Write an INSERT ... SELECT that copies every customer from India into a customers_india table.
3. What happens if you insert an employee with dept_id = 99, and which constraint stops it?