SQL INSERT INTO Statement Explained with Examples | DbSchema

SQL INSERT INTO adds new rows to a table. It takes two forms: name the columns explicitly and pass a matching list of values, or omit the column list and supply a value for every column in table order. A third form, DEFAULT VALUES, inserts a row using the defaults the table already declares. Each form is covered below with its syntax, a worked example and the resulting table.

What INSERT INTO does and its two forms

INSERT INTO adds new rows of data to a database table. The examples below use a Users table with four columns, so you can see exactly what each form of the statement writes.

Sample Database Table: Users

Check the table structure before inserting

Before inserting data into a table, it's crucial to understand the structure, including column names and their respective data types. Both MySQL and PostgreSQL provide functionalities to inspect a table's structure.

MySQL

The DESCRIBE statement is used.

Syntax:

DESCRIBE table_name;

Example:

DESCRIBE Users;

This command will display the columns of the Users table, their data types, and other characteristics.

PostgreSQL

The \d command in the psql command-line client is used.

\d table_name;

Example:

\d Users;

Similar to MySQL's DESCRIBE, this command will reveal the structure of the Users table in PostgreSQL.

Data types and value formats

When inserting data into a database, it's crucial to understand the formats expected by different data types, especially if you're moving between MySQL and PostgreSQL.

Remember, when working with SQL, always ensure your data types match the expected format of your specific database system. Failure to match the right formats can lead to errors or incorrect data being inserted.

INSERT INTO with explicit column names

At times, we need a fine-grained control over which columns we insert data into, especially when not all columns are mandatory or when we want to leave some columns to their default values. By specifying column names, we can ensure that the values get inserted into the right columns.

Syntax:

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

Example:

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

Result:

Explanation:

The Users table now has a new row with Emily Adams' details. This method ensures that the values are inserted into the specified columns in the correct order.

INSERT INTO with positional values

In scenarios where you have values for all columns and are certain of their order, you can simplify the statement by omitting column names. This method is faster but requires extra caution.

Syntax:

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

Example:

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

Result:

Explanation:

Mike Brown's details are directly inserted into the Users table. This approach is straightforward but assumes values for all columns in their defined order.

INSERT INTO using DEFAULT VALUES

When working with tables, certain columns may have default values specified. This can be useful in scenarios where a specific value is common and should be automatically filled in if not provided. By using the DEFAULT VALUES keyword, you can swiftly insert a new row where all the columns take on their respective default values.

Syntax:

INSERT INTO table_name
DEFAULT VALUES;

Consider a modified version of our Users table where there's a default value for the Age column. Let's assume that if no age is provided, the default is set to 18.

Example:

Imagine you want to insert a record for a user named 'Lucy Gray', but you don't have her age. In such a case, the database will use the default value for the age column.

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

Result:

Explanation:

As we didn't provide an age for Lucy Gray, the database employed the default age value of 18 for her. Thus, our table now holds five records, with Lucy's age defaulted to 18.

INSERT INTO... SELECT: copying rows between tables

INSERT INTO ... SELECT copies rows from one table into another in a single statement, instead of listing values by hand. It is the usual way to migrate data, take a working backup of a subset, or populate a summary table. The full treatment - copying every row, copying selected columns, avoiding duplicates, and the TOP / LIMIT / ORDER BY differences between engines - is covered in the SQL INSERT INTO SELECT statement tutorial. If you need the destination table created as part of the copy instead of inserting into one that already exists, see the SQL SELECT INTO statement.

Design and visualizeyour database schema

Edit referenced recordsin related tables

Query your datavisually too

Reuse the SQLgenerated

Free Download

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

Before inserting large batches, run a quick validation on destination columns, defaults, and key constraints. DbSchema helps you inspect table structure and execute INSERT queries safely in one place.

Common mistakes and the errors they raise

• Mismatching Column Order: Ensure the order of columns matches with the values you're providing.
• Duplicate Primary Key: Ensure you're not inserting a row with a primary key value that already exists.
• Data Type Mismatch: Ensure that data types of the values being inserted match with the column data types.

FAQ

Q: Can I insert multiple rows in a single statement?

A: Yes. You can provide multiple rows of values separated by commas.

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

Q: Do I always need to use the VALUES keyword?

A: When inserting data directly, yes. But when copying from another table using an INSERT INTO SELECT statement, no.

Practice questions

1. How would you insert a new user named "Tom Rogers" aged 45 into the Users table without specifying column names?
2. Insert user "Lucy Gray" with UserID 7 and Age 29 into the Users table by specifying column names.
3. Copy all users above the age of 30 from the Users table to another table named 'OlderUsers'.
4. Write a SQL query to insert the following details into an Employees table: ID - 1001, Name - Robert Smith, JobRole - Manager.
5. Modify your previous query to insert Name and JobRole only, without specifying an ID.
6. Given a Customers table and an Orders table, how would you insert the details of all customers living in New York from the Customers table into the Orders table?
7. What SQL command would you use to insert multiple rows of data into a Products table?

Conclusion

INSERT INTO is the statement to reach for whenever rows have to be written by hand, seeded, or copied between tables. Use the column-list form when the table may gain columns later, the positional form for short fixed tables, and DEFAULT VALUES when the table already declares the value you want.

Download DbSchema and connect it to your own database to run these statements against a real schema. The free Community Edition covers connecting, reverse-engineering the schema into an interactive diagram, and running INSERT statements in the SQL editor.