Summary: in this tutorial, you will learn how to use SQL INSERT statement to insert data into tables.
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,...);
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
- 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
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
INSERT statement inserts a new row into the
INSERT INTO shippers(companyname, phone) VALUES ('Alliance Shippers','1-800-222-0451');
'Alliance Shippers' and
'1-800-222-0451' are specified in the
VALUES clause. The database engine inserted them into the
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,...)
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.
INSERT statement has the same effect as the one above:
INSERT INTO shippers VALUES ('Alliance Shippers','1-800-222-0451');
SQL INSERT statement – insert multiple rows into a table
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,…), …
In this form, you need to provide multiple lists of values, each list is separated by a comma.
INSERT statement inserts two rows into the
INSERT INTO shippers(companyName,phone) VALUES ('UPS','1-800-782-7892'), ('DHL','1-800-225-5345')
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
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 shippers
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.