SQL INSERT INTO Statement Explained with Examples

You created a table, and it holds nothing yet. INSERT INTO is the statement that puts rows in: you name the table, list the columns you are filling, and hand over the values in that order. A shorter form skips the column list, and a third form asks the table for its own defaults. The choice between them decides what happens the day somebody adds a column.

Two tables carry the examples, and the second INSERT seeds the first one with two rows:

CREATE TABLE Users (
    UserID    INT PRIMARY KEY,
    FirstName VARCHAR(30),
    LastName  VARCHAR(30),
    Age       INT DEFAULT 18
);

CREATE TABLE ArchivedUsers (
    UserID    INT PRIMARY KEY,
    FirstName VARCHAR(30),
    LastName  VARCHAR(30),
    Age       INT
);

INSERT INTO Users (UserID, FirstName, LastName, Age)
VALUES (1, 'John', 'Doe',   25),
       (2, 'Jane', 'Smith', 30);

What INSERT INTO does and its two forms

The two-row statement above already shows two things. Several rows go in at once when you write more than one parenthesized list after VALUES, separated by commas, and the whole statement either succeeds or writes nothing. Users now holds:

The two forms differ only in whether the column list is there. With it, you choose which columns you fill and in what order. Without it, you are promising a value for every column, in the order the table declares them.

Check the table structure before inserting

The column list you are allowed to write is the one the table declares, and for the positional form the order in the table definition is the order of your values. Both are worth reading off the database rather than remembering. MySQL prints them with DESCRIBE:

DESCRIBE Users;

The psql client prints the same information for PostgreSQL with a backslash command rather than a statement:

\d Users

Either way you get the column names, the data type of each one, whether it accepts NULL, and the default it falls back on.

Data types and value formats

A value has to be written in the form its column's type expects, and the rules are short:

Only the last row differs between engines. PostgreSQL 18 has a real boolean type. In MySQL 8.4, BOOL and BOOLEAN are synonyms for TINYINT(1), and TRUE and FALSE are, in the manual's words, "merely aliases for 1 and 0"[1]. Both engines accept TRUE in an INSERT; MySQL stores a 1 and hands you a 1 back.

INSERT INTO with explicit column names

The column list pairs each name with the value in the same position, so the two lists have to be the same length:

INSERT INTO Users (UserID, FirstName, LastName, Age)
VALUES (3, 'Emily', 'Adams', 27);

The order of the list is yours to pick. Write it as (LastName, FirstName, UserID, Age), rearrange the values to match, and exactly the same row goes in. A column you leave out of the list is not left empty either, it takes its declared default, or NULL where there is none[2].

INSERT INTO with positional values

The positional form leaves the column list out, and the values line up against the table definition instead:

INSERT INTO Users
VALUES (4, 'Mike', 'Brown', 22);

It is shorter, and it is the form to avoid in anything you keep. Every statement written this way breaks the day a column is added to Users. The ones that break loudly are the lucky ones: add the column in the middle and the values still count out, but each one lands in the wrong place. Use the positional form for a throwaway query on a table you are looking at right now, and write the column list everywhere else.

INSERT INTO using DEFAULT VALUES

Users.Age was declared DEFAULT 18, so leaving Age out of the column list fills it in:

INSERT INTO Users (UserID, FirstName, LastName)
VALUES (5, 'Lucy', 'Gray');

Writing the keyword DEFAULT in place of a value in the VALUES list does the same thing for one column while you spell out the others[2]. DEFAULT VALUES is the whole-row version:

INSERT INTO Users
DEFAULT VALUES;

Every column takes its default, which means the form only fits a table where every column has one. Users is not such a table: UserID is its primary key, it has no default, and a row without one has no identity. MySQL spells this form differently, with an empty column list and an empty VALUES list, INSERT INTO Users () VALUES();[3].

Copying rows between tables with INSERT INTO SELECT

Rows that already exist somewhere else do not have to be typed out. Replace the VALUES list with a SELECT, and every row it returns is inserted:

INSERT INTO ArchivedUsers (UserID, FirstName, LastName, Age)
SELECT UserID, FirstName, LastName, Age
FROM Users
WHERE Age > 25
ORDER BY UserID;

ArchivedUsers was empty, and the SELECT handed it the two rows the WHERE clause kept, sorted as the ORDER BY asks:

The columns pair up by position again, this time between the insert list and the select list, and the names on the two sides need not match. That is the usual way to migrate rows, to keep a working copy of a subset, or to fill a summary table. Copying every row, skipping duplicates, and the row-limit differences between engines are in the SQL INSERT INTO SELECT statement tutorial. To have the destination table created by the copy instead of inserting into one that exists, see the SQL SELECT INTO statement.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Test INSERT workflows in DbSchema

An INSERT that fails usually fails on something the statement doesn't show you: a default you forgot, a type that doesn't match, a foreign key pointing at a row that isn't there. DbSchema connects to the database, reverse-engineers it, and draws the tables with their columns, types and foreign keys on a diagram, so those constraints are in front of you while you write. Paste the statement into the SQL Editor and Execute Query runs it against the live database.

For rows you would rather type than compose, open the table in the Relational Data Editor and click Insert in the table footer, which gives you an edit form with one field per column. Selecting a row in a parent pane filters the child panes to the records that match it, so the rows a new one has to line up with are on screen while you fill the form in. To load a file instead, the Data Importer reads CSV, TSV and Excel files into a table; open it from "Data Tools → Import Data From File", or right-click a table header and choose "Import Data From File". The SQL Editor and the diagram are in the free Community Edition, and the Relational Data Editor and the Data Importer are Pro. All three write rows to the live database and leave the design model file untouched; in the SQL Editor and the Relational Data Editor, an INSERT becomes permanent when you press Commit.

Common mistakes when inserting rows

Three mistakes account for most rejected INSERT statements. The value list and the column list have different lengths, which the database catches immediately. The values are in the wrong order, which it catches only when a type disagrees, so a first name in the last name column goes in quietly. Or the primary key value already exists in the table, and the row is refused because a key identifies one row and one only.

A fourth one depends on a setting rather than on the statement: a value that does not fit its column's type. MySQL 8.4 runs with strict mode in its default SQL mode, and a string such as 'abc' in an integer column is then rejected with error 1366, "Incorrect integer value". Switch strict mode off and MySQL writes an adjusted value and reports 1366 as a warning instead[4].

Practice questions

Five statements to write against Users and ArchivedUsers:

1. Insert Tom Rogers, aged 45, with UserID 6, without writing a column list.
2. Insert Nina Fox with UserID 7 and no age, and say what her Age column holds afterwards.
3. Insert two rows in a single statement.
4. Copy every user under 25 from Users into ArchivedUsers.
5. Rewrite question 1 with a column list, in the order LastName, FirstName, Age, UserID.

Reach for the column-list form whenever the statement will outlive the afternoon, the positional form only on a table you are looking at, and DEFAULT VALUES when the table already declares what you want. Every statement on this page runs unchanged in DbSchema's SQL Editor, with the columns, the types and the defaults of the target table on the diagram beside it, and both of those are in the free Community Edition. The Relational Data Editor and the Data Importer are in Pro. Download DbSchema at https://dbschema.com/download.html and start with the two-row statement at the top of this page.

FAQ

Can I insert multiple rows in a single statement?

Write several parenthesized value lists after VALUES, separated by commas. Each list has to hold as many values as the column list does, and any value in it can be the keyword DEFAULT.

INSERT INTO Users (UserID, FirstName, LastName, Age)
VALUES (8, 'Anna', 'Davis', 28),
       (9, 'Sam',  'Jones', 32);

Do I always need the VALUES keyword?

Only when you are supplying the values yourself. An INSERT INTO SELECT statement puts a query where the VALUES list would go, and DEFAULT VALUES replaces it with the table's own defaults.