Lesson 9 — Adding Data

INSERT

INSERT adds new rows to a table. Understanding column lists, default values, and multi-row inserts keeps your data loading efficient and correct.

SQL — Basic INSERT 
-- Basic INSERT with column list (recommended — explicit and safe):
INSERT INTO learners (full_name, age, city, course, mark)
VALUES ('Thandi Mokoena', 21, 'Johannesburg', 'Java', 74.50);

-- Without column list (fragile — must match every column in exact order):
INSERT INTO learners
VALUES (DEFAULT, 'Sipho Dlamini', '9901015800082', 24, 'Cape Town',
        'Western Cape', 'Python', 81.00, 'sipho@email.co.za', '0821234567',
        TRUE, '2024-01-15', DEFAULT, DEFAULT);
-- DEFAULT keyword tells the DB to use the column's default value

-- Always use the column list form — it works even when columns are added later
SQL — Multi-row INSERT 
-- Multi-row INSERT (much more efficient than multiple single inserts):
INSERT INTO learners (full_name, age, city, course, mark)
VALUES
    ('Lerato Khumalo',  20, 'Durban',       'SQL',    68.00),
    ('Bongani Nkosi',   22, 'Bloemfontein', 'C#',     88.50),
    ('Zanele Moyo',     19, 'Pretoria',     'Java',   62.00),
    ('Nomsa Shabalala', 25, 'Johannesburg', 'Python', 79.00),
    ('Andile Molefe',   23, 'Cape Town',    'SQL',    71.50);
-- One statement inserts 5 rows — far faster than 5 separate INSERT statements
SQL — Advanced INSERT 
-- INSERT with SELECT — copy rows from another table:
INSERT INTO learners_archive (full_name, course, mark, archived_date)
SELECT full_name, course, mark, CURRENT_DATE
FROM   learners
WHERE  is_active = FALSE;

-- Retrieve the auto-generated ID after insert:
-- MySQL:
SELECT LAST_INSERT_ID();

-- PostgreSQL (use RETURNING clause):
INSERT INTO learners (full_name, age, course)
VALUES ('New Learner', 20, 'Java')
RETURNING learner_id;

-- SQL Server:
INSERT INTO learners (full_name, age, course)
VALUES ('New Learner', 20, 'Java');
SELECT SCOPE_IDENTITY() AS new_id;

Practice Task

Your Turn

Write INSERT statements to: (1) add a single learner with all columns specified; (2) add 4 more learners in one multi-row INSERT; (3) insert a course record that auto-generates its ID; (4) copy all learners with mark below 50 into a remedial_learners table using INSERT ... SELECT.

Common Mistakes

  • Inserting without a column list — adding a new column to the table will break all your inserts.
  • String values must be in single quotes — double quotes are for identifiers in standard SQL.
  • Inserting NULL for a NOT NULL column — the database rejects it with a constraint error.
  • Multi-row INSERT that fails partway — the whole statement rolls back (in most databases). Wrap in a transaction if you need to know which rows succeeded.

Professional Tip

Always specify the column list in INSERT statements. This protects your code against schema changes and makes the insert self-documenting — you can see exactly what data is being stored.

Mini Quiz

What is the advantage of a multi-row INSERT over multiple single INSERT statements?