SQL INSERT

Summary: in this tutorial, you will learn how to use SQL INSERT statement to insert data into tables.

The INSERT statement inserts one or more rows into a table. The INSERT statement is sometimes referred to as an INSERT INTO statement.

SQL INSERT statement – insert one row into a table

The following illustrates the INSERT statement that inserts a single row into an existing table.

INSERT INTO table(column1, column2,...)
VALUES (value1, value2,...);Code language: SQL (Structured Query Language) (sql)

To insert a row into a table, you need to specify three things:

• First, the table, which you want to insert a new row, in the INSERT INTO clause.
• Second, a comma-separated list of columns in the table surrounded by parentheses.
• Third, a comma-separated list of values surrounded by parentheses in the VALUES clause.

The list of columns must have the same number of elements as the list of values, or the database engine will issue an error.

Let’s take a look at the following shippers table:

The following INSERT statement inserts a new row into the shippers table:

INSERT INTO shippers(companyname, phone)
VALUES ('Alliance  Shippers','1-800-222-0451');Code language: SQL (Structured Query Language) (sql)

Two constants,   'Alliance Shippers' and  '1-800-222-0451' are specified in the VALUES clause. The database engine inserted them into the companyname and phone columns respectively.

After executing the statement, the database server returns a message to indicate the number of affected rows. In this case, we get a message “1 row affected” informed that a new row has been inserted successfully.

Notice that we didn’t specify the shipperID column in the columns list because the shipperID column is an AUTO INCREMENT column, the database engine generates the next sequence for it automatically whenever a new row is inserted into the table.

To help you write less code, SQL provides a shorter form of the INSERT statement as follows:

INSERT INTO table
VALUES(value1,value2,...)Code language: SQL (Structured Query Language) (sql)

In this form, the list of values must have the same order as the list of columns in the table. If you use this form of the INSERT statement, you must supply values for all columns except the  AUTO INCREMENT column.

It is good practice to use the column names in the INSERT statement to make the code easier to maintain.

The following INSERT statement has the same effect as the one above:

INSERT INTO shippers
VALUES ('Alliance  Shippers','1-800-222-0451');Code language: SQL (Structured Query Language) (sql)

SQL INSERT statement – insert multiple rows into a table

The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following:

INSERT INTO table_name(column1,column2…)
VALUES (value1,value2,…),
       (value1,value2,…),
…Code language: SQL (Structured Query Language) (sql)

In this form, you need to provide multiple lists of values, each list is separated by a comma.

The following INSERT statement inserts two rows into the shippers table:

INSERT INTO shippers(companyName,phone)
VALUES ('UPS','1-800-782-7892'),
       ('DHL','1-800-225-5345')Code language: SQL (Structured Query Language) (sql)

SQL INSERT statement – copy table data

Instead of specifying a list of values, you can use a SELECT statement to select values from another table and supply them to the INSERT statement. This allows you to copy data from a table to another table.

The following statement illustrates how to copy data from the  another_table to the table:

INSERT INTO table(column1, column2,...)
SELECT 
     column1, column2,...
FROM 
     another_table
WHERE 
     condition

The list of columns in the SELECT clause must be corresponding to the list of columns in the  INSERT INTO clause. If you want to copy only partial data, you need to specify a condition in the WHERE clause.

Suppose you have a temporary table named  shippers_tmp that has the same structure as the  shippers table. To copy data from the  shippers table to the  shippers_tmp table, you use the following statement:

INSERT INTO shippers_tmp (shipperid,name,phone)
SELECT 
    shipperid, companyName, phone
FROM 
    shippersCode language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the INSERT statement to insert one or more rows into a table. In addition, you also learned how to copy the data from a table to another table by using the  INSERT SELECT INTO statement.