SQL INSERT INTO Statement: Add New Rows to a Table (Examples)

SQL Tutorial · Chapter 25 of 32

The SQL INSERT INTO statement adds new rows to a table. You name the table, optionally list the columns you are filling, and supply a matching set of values for each new row. INSERT can add a single row, several rows in one statement, or copy rows from another query, and it is the first of the three data-modification commands alongside UPDATE and DELETE.

SQL INSERT INTO Syntax

-- specify columns (recommended)
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- all columns, in table order
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

-- several rows at once
INSERT INTO table_name (column1, column2)
VALUES (a1, a2), (b1, b2), (c1, c2);

Listing the columns protects the statement from breaking when the table structure changes and lets you skip columns that have defaults.

Sample Table (Before)

Example 1: Insert One Row

INSERT INTO Customers (CustomerID, Name, City, Country)
VALUES (6, 'Liam Walker', 'Toronto', 'Canada');

SELECT * FROM Customers WHERE CustomerID = 6;

Text values are quoted, numbers are not, and the values line up with the column list in order. The table now has six rows, and order 105 (CustomerID 6) finally has a matching customer.

Example 2: Insert Several Rows

INSERT INTO Customers (CustomerID, Name, City, Country)
VALUES (7, 'Sofia Rossi', 'Rome', 'Italy'),
       (8, 'Ahmed Khan', 'Dubai', 'UAE');

SELECT CustomerID, Name FROM Customers WHERE CustomerID > 5;

One statement, two rows. Multi-row inserts are much faster than separate statements because the database does the work in a single transaction.

Example 3: Insert into Some Columns Only

Columns you leave out receive their DEFAULT value, or NULL if none is defined. Here the City is unknown.

INSERT INTO Customers (CustomerID, Name, Country)
VALUES (9, 'Noor Ali', 'Egypt');

SELECT * FROM Customers WHERE CustomerID = 9;

Example 4: Insert a New Order with an Auto-Generated ID

Most tables generate their primary key automatically (AUTO_INCREMENT, IDENTITY, SERIAL). Leave the key out of the column list and the database assigns it.

INSERT INTO Orders (CustomerID, Product, Amount, OrderDate)
VALUES (5, 'Webcam', 60, '2026-03-10');

SELECT * FROM Orders WHERE Product = 'Webcam';

Emma Brown (customer 5) now has her first order.

Example 5: INSERT … SELECT (Copy Rows)

Instead of VALUES, use a SELECT to copy rows from one table into another. This archives all orders under 50 into a table with the same columns.

INSERT INTO SmallOrders (OrderID, CustomerID, Product, Amount, OrderDate)
SELECT OrderID, CustomerID, Product, Amount, OrderDate
FROM Orders
WHERE Amount < 50;

Works in MySQL, SQL Server, PostgreSQL, SQLite

• Single-row, multi-row and INSERT … SELECT all work in the four systems. SQL Server limits a single VALUES list to 1,000 rows.
• Getting the generated key back: MySQL LAST_INSERT_ID(), SQL Server SCOPE_IDENTITY() or OUTPUT INSERTED.OrderID, PostgreSQL RETURNING OrderID, SQLite last_insert_rowid() or RETURNING.
• Upserts differ: MySQL ON DUPLICATE KEY UPDATE, PostgreSQL and SQLite ON CONFLICT ... DO UPDATE, SQL Server MERGE.

Common Mistakes

• Column count does not match value count: “column count doesn’t match value count at row 1”.
• Inserting a duplicate primary key, which raises a unique constraint violation.
• Omitting a NOT NULL column that has no default.
• Relying on table column order with INSERT INTO t VALUES (...); a later ALTER TABLE silently shifts your values.
• Forgetting the foreign key: inserting an order for a CustomerID that does not exist fails when constraints are enforced.

Practice Exercise

1. Add a customer with ID 10, name ‘Yuki Tanaka’, city ‘Tokyo’, country ‘Japan’.
2. Add two orders for customer 10 in one statement: a Monitor for 280 and a Mouse for 30, both dated 2026-03-15.

-- 1
INSERT INTO Customers (CustomerID, Name, City, Country)
VALUES (10, 'Yuki Tanaka', 'Tokyo', 'Japan');

-- 2
INSERT INTO Orders (CustomerID, Product, Amount, OrderDate)
VALUES (10, 'Monitor', 280, '2026-03-15'),
       (10, 'Mouse', 30, '2026-03-15');

Related Chapters

• SQL UPDATE – change rows you have inserted
• SQL DELETE – remove rows
• SQL SELECT – the query behind INSERT … SELECT
• SQL from Scratch: full course overview

FAQ

Do I have to list the columns in an INSERT statement?

No, but you should. Without a column list the values must match every column in table order, and the statement breaks as soon as a column is added.

How do I insert multiple rows in one SQL statement?

Separate each row’s value list with a comma: VALUES (1, ‘A’), (2, ‘B’), (3, ‘C’). This works in MySQL, SQL Server, PostgreSQL and SQLite.

What happens if I insert a row with a duplicate primary key?

The database rejects it with a constraint error and no row is added. Use the database’s upsert syntax if you want to update the existing row instead.

Chapter 25 of 32 · SQL from Scratch: all 32 chapters