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;

-- Inserts one row into the employees table.
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('Taro Tanaka', 'Development', 300000, '2024-04-01');

-- Inserts multiple rows at once.
INSERT INTO employees (name, department, salary, hire_date)
VALUES
    ('Hanako Suzuki', 'Sales',       280000, '2024-04-01'),
    ('Ichiro Sato',   'General',     320000, '2024-04-01'),
    ('Jiro Yamada',   'Development', 350000, '2024-04-01');

-- Omitting the column list requires values for every column in order.
INSERT INTO employees VALUES (NULL, 'Misaki Takahashi', 'Sales', 260000, '2024-04-01', NULL);

-- Copies data from another table.
INSERT INTO employees_archive (name, department, salary)
SELECT name, department, salary FROM employees WHERE retire_date IS NOT NULL;

-- After INSERT executes, the number of affected rows is returned.
-- Example: after inserting 3 rows
-- Query OK, 3 rows affected (0.01 sec)
-- Records: 3  Duplicates: 0  Warnings: 0

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
    ('Hanako Suzuki', 'Sales',   280000, '2024-04-01'),
    ('Ichiro Sato',   'General', 320000, '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 ('Taro Tanaka') 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.