SQL Insert

The INSERT statement in SQL is used to add new rows to a table. It can be used to insert single rows, multiple rows, or data from another table.

---

Basic Syntax: Inserting a Single Row

To insert a new row into a table, specify the target columns and their values:

INSERT INTO orders (user_id, amount, status)
VALUES (1, 100.50, 'pending');

• The column order must match the order of the values.

• If inserting values for all columns, the column list can be omitted:

  INSERT INTO orders
  VALUES (DEFAULT, 1, 100.50, 'pending');

  • DEFAULT inserts the default value of order_id (e.g., SERIAL auto-increment).

---

Inserting Multiple Rows

To insert multiple records in a single statement:

INSERT INTO orders (user_id, amount, status)
VALUES 
    (2, 50.00, 'pending'),
    (3, 75.25, 'completed'),
    (1, 120.00, 'shipped');

This is more efficient than inserting each row separately.

---

Inserting Data from Another Table (INSERT ... SELECT)

You can insert data into a table by selecting from another table:

INSERT INTO archived_orders (order_id, user_id, amount, status)
SELECT order_id, user_id, amount, status 
FROM orders 
WHERE status = 'completed';

• This copies all completed orders into archived_orders.
• Useful for data migrations or backups.

---

Handling Conflicts (ON CONFLICT / UPSERT)

When inserting into a table with unique constraints, use ON CONFLICT to handle duplicates:

INSERT INTO users (user_id, username, email)
VALUES (5, 'new_user', 'new@example.com')
ON CONFLICT (user_id) 
DO NOTHING;

• DO NOTHING: Skips insertion if user_id already exists.

• DO UPDATE: Updates existing rows instead:

  INSERT INTO users (user_id, username, email)
  VALUES (5, 'new_user', 'new@example.com')
  ON CONFLICT (user_id) 
  DO UPDATE SET email = EXCLUDED.email;

  • EXCLUDED.email refers to the new value that was attempted.

---

Returning Inserted Data (RETURNING clause, PostgreSQL)

To retrieve inserted values (especially useful for SERIAL primary keys):

INSERT INTO orders (user_id, amount, status)
VALUES (1, 99.99, 'pending')
RETURNING order_id, status;

---

Links to Related Notes

• SQL Update - How to update data in an existing table
• SQL Create - How to create new tables
• SQL Transactions and Errors – Ensuring atomic operations.
• SQL keys (pk and fk) – Handling unique constraints, foreign keys, etc.

---

Final Notes

• Use batch inserts for performance.
• Use INSERT ... SELECT for copying data efficiently.
• Handle conflicts properly to prevent duplicate key errors.
• Use RETURNING (PostgreSQL) to fetch inserted data immediately.