SQL INSERT INTO statement

Basic INSERT statement

Use the INSERT INTO statement to insert a new record into a table with a simple query.

Below is an example table of customers with columns name (VARCHAR(20)), surname (VARCHAR(20)), zip_code (INT), and city (VARCHAR(10)).

To add a record about a new customer with the name Bobby, surname Ray, ZIP code 60601, and the city of Chicago, the query will look as below:

INSERT INTO customers (name, surname, zip_code, city) 
VALUES ('Bobby', 'Ray', 60601, 'Chicago');

List of values to be inserted after the keyword VALUES.

Once the above query is executed, the table customers will have a new row:

In case you know the exact order of the columns in the table, the previous SQL query can be rewritten like this:

INSERT INTO customers 
VALUES ('Bobby', 'Ray', 60601, 'Chicago');

Insert multiple rows

To insert multiple rows, there's no need to add them one by one. Multiple rows can be added simultaneously.

To add two more rows to the customers table, the query will be:

INSERT INTO customers (name, surname, zip_code, city) 
VALUES ('Mary', 'West', 75201, 'Dallas'), 
       ('Steve', 'Palmer', 33107, 'Miami');

This example has two comma-separated lists of values instead of just one.

Insert into specified columns

To insert a record with missing values, specify the column to fill in.

To add information about a three-year-old homeless cat Felix in table cats with columns name (VARCHAR(20)), age (INT), and owner (VARCHAR(40)). The INSERT INTO query will be:

INSERT INTO cats (name, age) 
VALUES ('Felix', 3);

As a result, the cats table cats will have one row:

The owner column value for the first row will be NULL since it's not specified.

If a column has a default value and a column is skipped during the insertion, its value will be set to default.

Basic INSERT INTO statement template

INSERT INTO table_name (column_1, column_2,..., column_n)
VALUES (list_of_values_1) [, (list_of_values_2), ..., (list_of_values_n)];

This notation means that one tuple of values is always necessary; others are optional.

When the order of columns is well known, to insert values into all the columns, follow the shorter INSERT INTO statement template: 

INSERT INTO table_name
VALUES (value_1, value_2,..., value_n);

Inserting selected rows

Let's keep our initial example and consider the customers table with columns customer VARCHAR(40), email VARCHAR(50), and zip_code INT:

There's the users table with columns user VARCHAR(40), user_email VARCHAR(50), zip_code INT and city VARCHAR(20), that already has one pre-existing row:

To move the information from the customers column to the column users, from email to user_email, and from zip_code to zip_code, use the INSERT INTO SELECT statement.

The query below will insert all the needed data to the table users:

INSERT INTO users (user, user_email, zip_code) 
SELECT * FROM customers;

Once this query is executed, the users table will look as follows:

The customers table will not change: the rows are copied and not moved.

New rows were added to the end of the users table, as the new information was added with a simple INSERT INTO query.

INSERT INTO SELECT with WHERE

When writing the INSERT INTO SELECT statement, use WHERE in the nested SELECT query.

Now, let's add information from the suppliers table with columns supplier VARCHAR(40), city VARCHAR(20), supplier_email VARCHAR(45), and zip_code INT:

To add only the information about Tomato Inc the query will be:

INSERT INTO users
SELECT 
    supplier, 
    supplier_email,
    zip_code,
    city 
FROM 
    suppliers
WHERE
    supplier = 'Tomato Inc';

Here, the columns are preordered in the SELECT statement for the INSERT INTO statement because the order of columns doesn't match.

After the query execution, there's a new row in the table users:

The types of columns user_email and supplier_email aren't the same: VARCHAR(50) for user_email and VARCHAR(45) for supplier_email. Data can be transferred because these types are compatible, so checking whether the types are compatible is required. In case they won't be compatible, an error can occur.

Standard transfer data query template

INSERT INTO table1 (column_1, column_2, ..., column_n)
SELECT 
    column_1,
    column_2,
    ...,
    column_n 
FROM 
    table2
WHERE 
    condition; 

‍