INSERT — Adding Test Data

So far every query has read data. Chapter 4 is about changing it — inserting, updating, deleting — and we start with INSERT, the command that adds new rows to a table. For testers, INSERT is mostly about test-data setup: getting the database into the state you need before a test runs. That sounds boring, but learning to seed test data with a few SQL statements is one of the biggest productivity wins in test automation.

How an INSERT runs

The basic INSERT

INSERT INTO users (name, email, role)
VALUES ('Test User', 'testuser@test.com', 'tester');

Three things to notice:

• Column list. (name, email, role) says "I'm providing values for these three columns." Anything not in that list — id, is_active, created_at — is filled by the column's default. The id auto-increments to the next number, is_active defaults to TRUE, created_at defaults to the current timestamp.
• VALUES list. The order of values matches the order of columns. Get them out of order and you'll insert the email into name and vice versa — and the database won't catch it (both are strings).
• No WHERE clause. INSERT creates new rows; it doesn't modify existing ones, so there's nothing to filter.

Verify the insert worked:

SELECT id, name, email, role, is_active, created_at
FROM users
WHERE email = 'testuser@test.com';

Result:

| id | name      | email             | role   | is_active | created_at          |
|----|-----------|-------------------|--------|-----------|---------------------|
| 6  | Test User | testuser@test.com | tester | TRUE      | 2026-05-06 11:14:22 |

A new row, with the database filling in id = 6, is_active = TRUE, and created_at = now.

Inserting multiple rows in one statement

For larger seeding, batch the INSERT — one statement, many rows:

INSERT INTO products (name, price, category_id, in_stock) VALUES
  ('Test Product 1', 29.99, 1, TRUE),
  ('Test Product 2', 49.99, 2, TRUE),
  ('Test Product 3',  9.99, 1, FALSE);

Three rows in one round trip — much faster than three separate INSERT statements when you're loading more than a handful of records. Most databases support this; SQL Server is the main exception (and even there, modern versions accept it).

INSERT … SELECT — copy data from another query

You can feed an INSERT from a SELECT instead of literal VALUES. Useful for cloning data:

-- Create a "test_" copy of every admin
INSERT INTO users (name, email, role)
SELECT
    'Test_' || name,
    'test_' || email,
    role
FROM users
WHERE role = 'admin';

A small dialect note: || is the standard SQL string-concatenation operator (PostgreSQL, SQLite). MySQL prefers CONCAT('Test_', name) instead. If you're writing portable SQL, CONCAT(...) is the safer bet.

Getting the inserted id

You'll often want the id of the row you just inserted — to tie it to a related INSERT, or to verify by id. The mechanism is database-specific:

PostgreSQL's RETURNING is the most ergonomic — the INSERT itself returns the new id without a follow-up query:

INSERT INTO orders (user_id, total, status)
VALUES (1, 99.99, 'pending')
RETURNING id;
-- Result: 6

When INSERT will refuse you

The database enforces constraints on every INSERT. The three you'll meet most often:

-- 1. UNIQUE constraint violation
-- email is UNIQUE, and alice@example.com already exists
INSERT INTO users (name, email, role)
VALUES ('Imposter', 'alice@example.com', 'tester');
-- ERROR: duplicate key value violates unique constraint

-- 2. FOREIGN KEY violation
-- user_id 9999 doesn't exist in users
INSERT INTO orders (user_id, total, status)
VALUES (9999, 50.00, 'pending');
-- ERROR: foreign key constraint failed

-- 3. NOT NULL violation
-- name is NOT NULL, and we didn't provide one
INSERT INTO users (email, role)
VALUES ('halfdone@test.com', 'tester');
-- ERROR: NULL value violates NOT NULL constraint

Each error is the database doing its job — protecting the data from being corrupted in ways the schema explicitly forbids. A good test of the schema, in fact, is to try to insert violating data and confirm the database rejects it.

A real test-data setup

Before a UI test that exercises the checkout flow, seed three users and two products in one go:

INSERT INTO users (name, email, role) VALUES
  ('Test Buyer 1', 'buyer1+ci@qa.local', 'tester'),
  ('Test Buyer 2', 'buyer2+ci@qa.local', 'tester'),
  ('Test Buyer 3', 'buyer3+ci@qa.local', 'tester');
 
INSERT INTO products (name, price, category_id, in_stock) VALUES
  ('Test Item A', 19.99, 1, TRUE),
  ('Test Item B', 49.99, 1, TRUE);

Three users, two products, six lines of SQL. Compare to clicking through "Sign Up" three times and "Create Product" twice in the UI — the SQL approach is faster, repeatable, and doesn't depend on the UI working. That's why testers learn INSERT.

A pattern worth adopting: every test-data row gets a recognisable suffix (+ci@qa.local, Test_*, qa-2026-05-06-*). Cleanup is then a one-line DELETE filtered on that suffix — covered later this chapter.

⚠️ Common Mistakes

• Skipping the column list. INSERT INTO users VALUES ('Alice', 'alice@x.com', ...) relies on column order in the schema. Add a column tomorrow and every INSERT statement breaks. Always name the columns explicitly.
• Inserting test data into production by accident. Most production incidents start with someone running INSERT INTO users (...) VALUES ('Test', ...) with the production connection selected. Confirm your connection string before any INSERT — and never give your test framework production credentials.
• Forgetting to verify. An INSERT that succeeds tells you the syntax was valid. Run a SELECT to confirm the row is there, with the values you expected. Two seconds of paranoia saves an hour of "why is the test flaky?".

🎯 Practice Task

20 minutes. All against the e-commerce database.

1. Insert one new user — 'QA Test', 'qa-test@local', 'tester'. Verify with a SELECT.
2. Insert three products in one statement — pick names, prices, and categories of your choosing. SELECT them back to confirm.
3. Insert an order for the user you created in step 1. Use LAST_INSERT_ID(), RETURNING id, or last_insert_rowid() (whichever your database supports) to capture the new order id.
4. Insert two order_items referencing that order, each pointing at one of the products from step 2.
5. Try to insert a user with the email 'alice@example.com' (which already exists). Confirm the UNIQUE constraint rejects it. Read the error.
6. Try to insert an order with user_id = 9999. Confirm the FOREIGN KEY constraint rejects it. Read the error.
7. Stretch: write an INSERT … SELECT that creates a 'shadow' copy of every product ('shadow_' || name, same price, same category). Confirm the products count doubled. Then DELETE the shadows — but DELETE is the next-but-two lesson, so for now just observe how easy duplication is and how important cleanup is.

Next lesson: UPDATE — modifying existing rows safely, and the WHERE-clause habit that has saved more careers than any other in SQL.