INSERT INTO

// Part 01

Writing Data Into a Table

// Part 02

Basic Syntax — Single Row Insert

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- Real example: add a new customer
INSERT INTO customers (
  first_name,
  last_name,
  email,
  phone,
  city,
  state,
  zip_code,
  joined_date,
  loyalty_tier
)
VALUES (
  'Priya',
  'Nair',
  'priya.nair@gmail.com',
  '9876543210',
  'Kochi',
  'Kerala',
  '682001',
  '2024-04-10',
  'Bronze'
);

-- WRONG: positional — breaks if schema changes, unreadable
INSERT INTO customers VALUES (
  DEFAULT, 'Priya', 'Nair', 'priya@gmail.com',
  '9876543210', 'Kochi', 'Kerala', '682001',
  '2024-04-10', 'Bronze'
);

-- RIGHT: named — self-documenting, schema-change resistant
INSERT INTO customers (first_name, last_name, email, city, loyalty_tier)
VALUES ('Priya', 'Nair', 'priya@gmail.com', 'Kochi', 'Bronze');
-- Omitted columns get their DEFAULT values or NULL

// Part 03

Multi-Row INSERT — Adding Many Rows at Once

INSERT INTO table_name (col1, col2, col3)
VALUES
  (val1a, val2a, val3a),
  (val1b, val2b, val3b),
  (val1c, val2c, val3c),
  ...;

-- Real example: add multiple products at once
INSERT INTO products (product_name, category, brand, unit_price, cost_price, unit, in_stock)
VALUES
  ('Amul Taaza Milk 1L',  'Dairy',   'Amul',  62.00, 52.00, '1L',  true),
  ('Tata Salt 1kg',       'Staples', 'Tata',  22.00, 16.00, '1kg', true),
  ('Maggi Noodles 70g',   'Staples', 'Nestle',15.00, 10.00, '70g', true),
  ('Fortune Sunflower Oil','Cooking', 'Fortune',145.00,118.00,'1L', false);

🎯 Pro Tip

In application code that needs to insert many rows — user registrations from a batch import, order items for a cart checkout, events from a stream — always batch inserts rather than inserting one row per database call. Most ORMs and database drivers support batch insert. In Python with psycopg2: executemany() or execute_values(). In Node.js: use a parameterised multi-row INSERT. Batching 100-1000 rows per INSERT is a standard production optimisation.

// Part 04

INSERT INTO ... SELECT — Copy Data Between Tables

INSERT INTO target_table (col1, col2, col3)
SELECT expr1, expr2, expr3
FROM source_table
WHERE condition;

-- The SELECT can be any valid query:
-- joins, aggregations, calculations, subqueries
-- The column count and types must match between
-- the INSERT column list and the SELECT output

-- Step 1: copy old cancelled orders to archive table
INSERT INTO orders_archive (
  order_id, customer_id, store_id, order_date,
  order_status, total_amount, archived_at
)
SELECT
  order_id, customer_id, store_id, order_date,
  order_status, total_amount, NOW()
FROM orders
WHERE order_status = 'Cancelled'
  AND order_date < CURRENT_DATE - INTERVAL '1 year';

-- Step 2: delete the archived rows from the main table
DELETE FROM orders
WHERE order_status = 'Cancelled'
  AND order_date < CURRENT_DATE - INTERVAL '1 year';

-- Always do Step 1 before Step 2 — archive first, then delete
-- Wrap both in a transaction for atomicity

// Part 05

RETURNING — Get Back What You Just Inserted

-- Insert and get back the generated customer_id
INSERT INTO customers (first_name, last_name, email, city, joined_date)
VALUES ('Kavya', 'Reddy', 'kavya@gmail.com', 'Austin', '2024-04-10')
RETURNING customer_id, first_name, joined_date;

-- Returns:
-- customer_id | first_name | joined_date
-- 21          | Kavya      | 2024-04-10

-- RETURNING * returns all columns of the inserted row
INSERT INTO products (product_name, category, unit_price, cost_price, in_stock)
VALUES ('Haldiram Bhujia 200g', 'Snacks', 80.00, 58.00, true)
RETURNING *;

-- RETURNING is also available on UPDATE and DELETE
-- UPDATE customers SET loyalty_tier = 'Gold' WHERE customer_id = 5
-- RETURNING customer_id, loyalty_tier;

// Part 06

ON CONFLICT — Upsert Patterns

-- If a customer with this email already exists, do nothing
-- Without this, a duplicate email INSERT would throw an error
INSERT INTO customers (first_name, last_name, email, city, joined_date)
VALUES ('Aisha', 'Khan', 'aisha.khan@gmail.com', 'Seattle', '2024-01-05')
ON CONFLICT (email) DO NOTHING;

-- The (email) specifies WHICH unique constraint to check
-- If email already exists: row is silently skipped, no error
-- If email is new: row is inserted normally

-- Common use case: batch import where duplicates are expected
INSERT INTO products (product_name, brand, unit_price, in_stock)
SELECT product_name, brand, unit_price, in_stock
FROM products_import_staging
ON CONFLICT (product_name, brand) DO NOTHING;

-- If product already exists (by name+brand), update its price and stock
-- If product is new, insert it
INSERT INTO products (product_name, brand, unit_price, cost_price, in_stock)
VALUES ('Amul Butter 500g', 'Amul', 285.00, 245.00, true)
ON CONFLICT (product_name, brand)
DO UPDATE SET
  unit_price = EXCLUDED.unit_price,
  cost_price = EXCLUDED.cost_price,
  in_stock   = EXCLUDED.in_stock;

-- EXCLUDED refers to the row that was ATTEMPTED to be inserted
-- So EXCLUDED.unit_price = 285.00 (the new value you tried to insert)
-- SET unit_price = EXCLUDED.unit_price means: update to the new value

-- You can also set computed values or conditionally update:
DO UPDATE SET
  unit_price = EXCLUDED.unit_price,
  -- Only update in_stock if the new value is true
  in_stock   = CASE WHEN EXCLUDED.in_stock THEN true ELSE products.in_stock END;

-- MySQL equivalent of PostgreSQL's ON CONFLICT DO UPDATE
INSERT INTO products (product_name, brand, unit_price, in_stock)
VALUES ('Amul Butter 500g', 'Amul', 285.00, true)
ON DUPLICATE KEY UPDATE
  unit_price = VALUES(unit_price),
  in_stock   = VALUES(in_stock);

-- VALUES(column) refers to the value from the INSERT attempt
-- Similar to PostgreSQL's EXCLUDED.column

// Part 07

INSERT Safety — Preventing Data Corruption

-- WRONG: two separate inserts — if the second fails, the first stays
INSERT INTO orders (customer_id, store_id, order_date, payment_method, total_amount)
VALUES (5, 'ST001', '2024-04-10', 'UPI', 856.00);

INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)
VALUES (31, 3, 2, 428.00, 856.00);
-- If this fails, the order exists but has no items — data corruption

-- RIGHT: wrap in a transaction
BEGIN;
  INSERT INTO orders (customer_id, store_id, order_date, payment_method, total_amount)
  VALUES (5, 'ST001', '2024-04-10', 'UPI', 856.00)
  RETURNING order_id;  -- capture the generated order_id

  INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)
  VALUES (31, 3, 2, 428.00, 856.00);
COMMIT;
-- If either INSERT fails, ROLLBACK automatically undoes both

-- WRONG: string interpolation (SQL injection vulnerability)
name = request.get('name')  # user input
query = f"INSERT INTO customers (first_name) VALUES ('{name}')"
# If name = "'; DROP TABLE customers; --"
# The query becomes: INSERT INTO customers (first_name) VALUES ('');
# DROP TABLE customers; --')
# This DELETES THE ENTIRE CUSTOMERS TABLE

-- RIGHT: parameterised query (Python psycopg2)
cursor.execute(
  "INSERT INTO customers (first_name, email) VALUES (%s, %s)",
  (name, email)  # values passed separately, never interpolated
)

-- RIGHT: parameterised query (Node.js with pg)
await client.query(
  'INSERT INTO customers (first_name, email) VALUES ($1, $2)',
  [name, email]
)

-- The database driver handles escaping — user input can never
-- break out of the value context into the SQL structure

-- Step 1: run the SELECT and inspect results
SELECT customer_id, first_name, email, city
FROM customers_staging
WHERE import_batch = '2024-04-10'
LIMIT 20;

-- Step 2: check the count
SELECT COUNT(*) FROM customers_staging WHERE import_batch = '2024-04-10';

-- Step 3: check for potential FK violations
SELECT s.customer_id
FROM customers_staging s
LEFT JOIN customers c ON s.customer_id = c.customer_id
WHERE s.import_batch = '2024-04-10'
  AND c.customer_id IS NULL;  -- these would violate FK constraints

-- Step 4: only then run the INSERT
INSERT INTO customers (first_name, last_name, email, city)
SELECT first_name, last_name, email, city
FROM customers_staging
WHERE import_batch = '2024-04-10';

// Part 08

INSERT with DEFAULT and CURRENT Values

-- CURRENT_DATE and NOW() as values
INSERT INTO orders (customer_id, store_id, order_date, payment_method, total_amount)
VALUES (5, 'ST001', CURRENT_DATE, 'UPI', 856.00);
-- order_date gets today's date automatically

-- DEFAULT keyword — use the column's DEFAULT value explicitly
INSERT INTO customers (first_name, last_name, email, joined_date, loyalty_tier)
VALUES ('Rahul', 'Sharma', 'rahul@gmail.com', DEFAULT, DEFAULT);
-- joined_date gets CURRENT_DATE (its default)
-- loyalty_tier gets 'Bronze' (its default)

-- Computed values in VALUES
INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)
VALUES (31, 5, 3, 56.00, 3 * 56.00);
-- line_total computed from quantity * unit_price directly in the INSERT

-- Expression from another query
INSERT INTO orders (customer_id, store_id, order_date, payment_method, total_amount)
VALUES (
  (SELECT customer_id FROM customers WHERE email = 'rahul@gmail.com'),
  'ST001',
  CURRENT_DATE,
  'UPI',
  1200.00
);

// Part 09

Bulk Loading — COPY and Large Data Imports

-- Load data from a CSV file directly into a table
COPY customers (first_name, last_name, email, city, joined_date, loyalty_tier)
FROM '/tmp/new_customers.csv'
WITH (
  FORMAT CSV,
  HEADER true,       -- first row is headers, skip it
  DELIMITER ',',
  NULL 'NULL',       -- how NULLs are represented in the file
  QUOTE '"'
);

-- Export table data to a CSV file
COPY customers TO '/tmp/customers_export.csv'
WITH (FORMAT CSV, HEADER true);

-- COPY from application code (psycopg2)
with open('customers.csv', 'r') as f:
    cursor.copy_expert(
        "COPY customers (first_name, email) FROM STDIN WITH CSV HEADER",
        f
    )

-- COPY is 10-100x faster than INSERT for large datasets:
-- 1 million rows via INSERT: ~60 seconds
-- 1 million rows via COPY:   ~3 seconds

-- MySQL equivalent of PostgreSQL COPY
LOAD DATA INFILE '/tmp/new_customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS   -- skip header row
(first_name, last_name, email, city, @joined_date, loyalty_tier)
SET joined_date = STR_TO_DATE(@joined_date, '%Y-%m-%d');

// Part 10

What This Looks Like at Work

-- Check row count
SELECT COUNT(*) FROM stores_staging WHERE approved = true;
-- Returns: 500

-- Spot-check a few rows
SELECT * FROM stores_staging WHERE approved = true LIMIT 5;

-- Check for NULL values in required fields
SELECT COUNT(*) FROM stores_staging
WHERE approved = true
  AND (store_name IS NULL OR city IS NULL OR monthly_target IS NULL);
-- Returns: 0 — all required fields populated

-- Check for duplicate store codes
SELECT store_code, COUNT(*)
FROM stores_staging
WHERE approved = true
GROUP BY store_code
HAVING COUNT(*) > 1;
-- Returns: 0 rows — no duplicates

-- How many staging stores already exist in production?
SELECT COUNT(*)
FROM stores_staging s
JOIN stores p ON s.store_code = p.store_id
WHERE s.approved = true;
-- Returns: 12 — 12 stores already in production

BEGIN;

INSERT INTO stores (
  store_id, store_name, city, state,
  manager_name, opened_date, monthly_target
)
SELECT
  store_code,
  store_name,
  city,
  state,
  manager_name,
  CURRENT_DATE,
  monthly_target
FROM stores_staging
WHERE approved = true
ON CONFLICT (store_id) DO NOTHING;

-- Verify: should have inserted 488 new stores (500 - 12 existing)
SELECT COUNT(*) FROM stores WHERE store_id IN (
  SELECT store_code FROM stores_staging WHERE approved = true
);

COMMIT;

🎯 Pro Tip

The verify-before-insert pattern is not optional for production bulk loads. Always: (1) inspect the staging data for NULLs in required fields, (2) check for internal duplicates within the staging data, (3) check for conflicts with existing production data, (4) run the INSERT inside a transaction, (5) verify the inserted count matches the expected count. Finding a data quality issue before the INSERT costs you 10 minutes. Finding it after the INSERT costs you hours of cleanup.

// Part 11

Interview Prep — 5 Questions With Complete Answers

// Part 12

Errors You Will Hit — And Exactly Why They Happen

What comes next

In Module 21, you learn UPDATE — modifying existing rows, updating multiple columns at once, updating from another table, and the most important safety rule in SQL: always run SELECT before UPDATE.