INSERT

Inserts one row. Specify column names paired with their corresponding values.

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

Inserts multiple rows at once.

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

Inserts the result of a SELECT query.

INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM other_table;

The following examples use the employees table (state before insertion).

Inserts one row into the employees table.

sample_insert.sql

INSERT INTO employees (name, department, salary, hire_date)
VALUES ('Masaoka Tomomi', 'Enforcer', 290000, '2024-04-01');

Query OK, 1 row affected (0.01 sec)

Verifies the insertion result.

sample_insert.sql

SELECT * FROM employees WHERE name = 'Masaoka Tomomi' ORDER BY id DESC LIMIT 1;

+----+----------------+------------+--------+------------+
| id | name           | department | salary | hire_date  |
+----+----------------+------------+--------+------------+
|  4 | Masaoka Tomomi | Enforcer   | 290000 | 2024-04-01 |
+----+----------------+------------+--------+------------+
1 row in set

Inserts multiple rows at once.

sample_insert.sql

INSERT INTO employees (name, department, salary, hire_date)
VALUES
    ('Karanomori Shion', 'Analyst', 310000, '2024-04-01'),
    ('Kogami Shinya', 'Enforcer', 300000, '2024-04-01'),
    ('Tsunemori Akane', 'Inspector', 280000, '2024-04-01');

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Verifies the insertion result. 3 rows have been added.

sample_insert.sql

SELECT * FROM employees ORDER BY id;

+----+------------------+------------+--------+------------+
| id | name             | department | salary | hire_date  |
+----+------------------+------------+--------+------------+
|  1 | Kogami Shinya    | Enforcer   | 300000 | 2023-04-01 |
|  2 | Tsunemori Akane  | Inspector  | 280000 | 2023-04-01 |
|  3 | Ginoza Nobuchika | Inspector  | 320000 | 2023-04-01 |
|  4 | Masaoka Tomomi   | Enforcer   | 290000 | 2024-04-01 |
|  5 | Karanomori Shion | Analyst    | 310000 | 2024-04-01 |
|  6 | Kogami Shinya    | Enforcer   | 300000 | 2024-04-01 |
|  7 | Tsunemori Akane  | Inspector  | 280000 | 2024-04-01 |
+----+------------------+------------+--------+------------+
7 rows in set

Omitting the column list requires values for every column in order. Passing NULL for the id column lets AUTO_INCREMENT assign the next number automatically.

sample_insert.sql

INSERT INTO employees VALUES (NULL, 'Ginoza Nobuchika', 'Inspector', 320000, '2024-04-01');

Query OK, 1 row affected (0.01 sec)

Verifies the insertion result.

sample_insert.sql

SELECT * FROM employees WHERE id = 8;

+----+------------------+------------+--------+------------+
| id | name             | department | salary | hire_date  |
+----+------------------+------------+--------+------------+
|  8 | Ginoza Nobuchika | Inspector  | 320000 | 2024-04-01 |
+----+------------------+------------+--------+------------+
1 row in set

Inserts a new employee with the same department and salary as an existing employee (example of using SELECT results in INSERT).

sample_insert.sql

INSERT INTO employees (name, department, salary, hire_date)
SELECT 'Karanomori Shion', department, salary, '2024-04-01'
FROM employees WHERE name = 'Ginoza Nobuchika' LIMIT 1;

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verifies the insertion result. The row was inserted with the same department and salary as Ginoza Nobuchika.

sample_insert.sql

SELECT * FROM employees WHERE id = 9;

+----+------------------+------------+--------+------------+
| id | name             | department | salary | hire_date  |
+----+------------------+------------+--------+------------+
|  9 | Karanomori Shion | Inspector  | 320000 | 2024-04-01 |
+----+------------------+------------+--------+------------+
1 row in set

The basic INSERT INTO ... VALUES syntax and multi-row insertion are supported by all major databases.

INSERT INTO employees (name, department, salary, hire_date)
VALUES
    ('Masaoka Tomomi', 'Enforcer', 290000, '2024-04-01'),
    ('Karanomori Shion', 'Analyst', 310000, '2024-04-01');

Auto-increment behavior differs by database. MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL or GENERATED AS IDENTITY, Oracle uses a sequence, SQL Server uses IDENTITY, and SQLite uses INTEGER PRIMARY KEY.

-- MySQL
CREATE TABLE employees (id INT NOT NULL AUTO_INCREMENT, ...);

-- PostgreSQL
CREATE TABLE employees (id SERIAL PRIMARY KEY, ...);
-- Or (SQL standard syntax)
CREATE TABLE employees (id INT GENERATED ALWAYS AS IDENTITY, ...);

-- SQL Server
CREATE TABLE employees (id INT IDENTITY(1,1) NOT NULL, ...);

The way to retrieve the auto-generated ID after an insert also varies. MySQL uses LAST_INSERT_ID(), PostgreSQL uses the RETURNING clause, SQL Server uses SCOPE_IDENTITY(), and Oracle uses RETURNING INTO.

-- MySQL
SELECT LAST_INSERT_ID();

-- PostgreSQL (can be appended directly to the INSERT statement)
INSERT INTO employees (name) VALUES ('Kogami Shinya') RETURNING id;

-- SQL Server
SELECT SCOPE_IDENTITY();

Use INSERT INTO to add new data to a table. Specifying column names explicitly makes the insertion independent of column order and more resilient to future schema changes that add new columns.

For a primary key column with AUTO_INCREMENT, omit the value or pass NULL to let the database assign the next sequential number automatically. To retrieve the assigned primary key after an insert, use the LAST_INSERT_ID() function in MySQL.

When running INSERT from application code, always use prepared statements. Embedding user input directly in an SQL string creates a risk of SQL injection attacks.