SQL INSERT

SQL INSERT statement allows you to insert one or more records into a database table. In addition SQL INSERT statement also allows you to copy data from a database tables to another database table.

Insert one record into a database table.

Here is the syntax of SQL INSERT statement which allows you to insert one record at a time into a database table.

INSERT INTO table_name (column1, column2…)
VALUES (value1, value2,…).

In this form, you specify the name of database table, which you want to insert data into, followed after the INSERT INTO statement. The number of columns (column1, column2,...) and values (value1, value2,..) must be same. If the column is omitted, the default value for column is used. The values inserting to the database table must also satisfy other conditions such as constraints of foreign key, NOT null constraints… otherwise the insert action will be failed and new row is not added to the database table.

Let’s take a look at an example of inserting data into the Shippers table by using SQL INSERT statement.

INSERT INTO Shippers (companyName, phone)
VALUES ('Alliance  Shippers','1-800-222-0451')

Because shipperID is the primary key of the Shippers table and it is automatically increased each time we insert a new row so we don’t need to list it there in the column list. Only data we have to provide for INSERT statement  are company name and phone. After executing the query, the database server returns number of row affected. Here you get 1 row affect to indicate that one row has been added successfully/

Insert multiple records into a database table

SQL INSERT statement also allows you to insert multiple records into a database table at a time. Here is the syntax:

INSERT INTO table_name(column1,column2…)
VALUES (value1,value2,…),
       (value1,value2,…),
…

In this form, you provide multiple values which are corresponding to the column list of the database table. Here is an example of inserting multiple records into Shippers table.

INSERT INTO shippers(companyName,phone)
VALUES ('UPS','1-800-782-7892'),
       ('DHL','1-800-225-5345')

Copy data from another database table

Sometime you need to copy data from one table into another table for backing up for example. SQL INSERT statement  allows you to copy data from another database tables to a database table. Here is the syntax:

INSERT INTO table_name(column1, column2,…)
SELECT value1, value2…
FROM table_name2
…

The selection list must be corresponding to columns of the database table you want to copy data.

Suppose you have a temporary table called table_tmp with the structure exactly the same as the shippers table. Now you want to copy data from the shippers table into this temporary table, you can use SQL INSERT INTO SELECT statement. In this case by performing the following query:

INSERT INTO shippers_tmp (companyName, phone)
SELECT companyName, phone
FROM shippers

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