Sometimes the UI is broken or incomplete. Maybe the registration page is not built yet, but you need users in the database to test the login flow. INSERT lets you create test data directly. This is one of the most powerful tools in a QA tester's toolkit.
-- Insert a single row
INSERT INTO users (first_name, last_name, email, status)
VALUES ('Priya', 'Sharma', 'priya@test.com', 'active');
-- Insert multiple rows at once (much faster)
INSERT INTO users (first_name, last_name, email, status)
VALUES
('Amit', 'Patel', 'amit@test.com', 'active'),
('Sneha', 'Reddy', 'sneha@test.com', 'active'),
('Rahul', 'Gupta', 'rahul@test.com', 'pending');
-- Insert with all columns (not recommended — fragile)
INSERT INTO users
VALUES (101, 'Kiran', 'Das', 'kiran@test.com', 'active', NOW(), NULL);Always specify column names in INSERT. If someone adds a new column to the table, INSERT without column names will break. Also, never insert test data into production databases without explicit permission from the team.
-- Scenario: Test login with different user states
INSERT INTO users (first_name, last_name, email, status, is_verified)
VALUES
('Active', 'User', 'active@test.com', 'active', true),
('Pending', 'User', 'pending@test.com', 'pending', false),
('Blocked', 'User', 'blocked@test.com', 'blocked', true),
('Deleted', 'User', 'deleted@test.com', 'deleted', false);
-- Scenario: Test boundary values for account balance
INSERT INTO accounts (user_id, account_type, balance)
VALUES
(1, 'savings', 0.00), -- zero balance
(2, 'savings', 0.01), -- minimum positive
(3, 'savings', 999999.99), -- near max
(4, 'savings', 1000000.00); -- at max limit
-- Scenario: Test with special characters in names
INSERT INTO users (first_name, last_name, email, status)
VALUES
('O''Brien', 'McDonald', 'obrien@test.com', 'active'),
('Jean-Pierre', 'De La Cruz', 'jp@test.com', 'active');Build a SQL script with all your test data INSERTs. Save it. Run it to set up test data before a test cycle. Run a cleanup DELETE script after. This is poor man's test data management — and it works.
Q: How do you handle auto-increment IDs when inserting test data?
A: Most databases auto-generate the primary key (id) when you insert. You do not specify it — just leave it out of your INSERT column list. If you need to know the generated ID, use LAST_INSERT_ID() in MySQL or RETURNING id in PostgreSQL. If you must insert a specific ID (for foreign key references in test data), you can specify it, but make sure it does not conflict with existing data.
Key Point: INSERT creates data directly. Always specify column names. Use it to set up test scenarios that the UI cannot create yet.