SQL INSERT Statements and Bulk Inserts

SQL INSERT Statements and Bulk Inserts

The INSERT statement adds new rows to a table. Understanding its variations is essential for efficient data loading and application development.

Single Row Insert

INSERT INTO employees (name, email, department, salary)
VALUES ('Alice Johnson', 'alice@example.com', 'Engineering', 95000);

Always specify column names explicitly. Relying on column order is fragile and breaks when the table schema changes.

Multi-Row Insert

INSERT INTO products (name, price, category)
VALUES
  ('Widget A', 29.99, 'Gadgets'),
  ('Widget B', 39.99, 'Gadgets'),
  ('Widget C', 49.99, 'Gadgets');

Multi-row inserts are significantly faster than individual INSERT statements because they reduce round-trips and transaction overhead.

INSERT SELECT

Copy data from one table or query into another:

INSERT INTO archived_orders (order_id, customer_id, total, order_date)
SELECT order_id, customer_id, total, order_date
FROM orders
WHERE order_date < '2024-01-01';

Upsert (INSERT ON CONFLICT)

PostgreSQL:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

MySQL:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Default Values and Auto-Increment

Omit auto-increment columns and columns with defaults:

INSERT INTO logs (message, level)
VALUES ('User logged in', 'INFO');

Bulk Insert Best Practices

• Use multi-row VALUES for moderate batches (100-1000 rows)
• For very large loads, use database-specific bulk tools (COPY in PostgreSQL, LOAD DATA in MySQL)
• Temporarily disable indexes and constraints during bulk loads, then rebuild
• Wrap large inserts in explicit transactions for atomicity and performance

INSERT is one of the most frequently used DML operations and mastering its variations is critical for building performant data pipelines.