SQL INSERT

Summary: in this tutorial, you will learn how to insert data into a table using the SQL INSERT statement.

Introduction to the SQL INSERT statement

SQL provides the INSERT statement that allows you to insert one or more rows into a table. The INSERT statement allows you to:

1. Insert a single row into a table
2. Insert multiple rows into a table
3. Copy rows from a table to another table.

We will examine each function of the INSERT statement in the following sections.

Insert one row into a table

To insert one row into a table, you use the following syntax of the INSERT statement.

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

There are some points that you should pay attention to when you insert a new row into a table:

• First, the number of values must be the same as the number of columns. In addition, the columns and values must be the correspondent because the database system will match them by their relative positions in the lists.
• Second, before adding a new row, the database system checks for all integrity constraints e.g., foreign key constraint, primary key constraint, check constraint and not null constraint. If one of these constraints is violated, the database system will issue an error and terminate the statement without inserting any new row into the table.

It is not necessary to specify the columns if the sequence of values matches the order of the columns in the table. See the following INSERT statement that omits the column list in the INSERT INTO clause.

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

However, this is not considering as a good practice.

If you don’t specify a column and its value in the INSERT statement when you insert a new row, that column will take a default value specified in the table structure. The default value could be 0, a next integer value in a sequence, the current time, a NULL value, etc. See the following statement:

INSERT INTO (column1, column3)
VALUES
	(column1, column3);Code language: SQL (Structured Query Language) (sql)

In this syntax, the column2 will take a default value.

Insert one row into a table example

We will use the employees and dependents tables in the sample database to show you how to insert one row into the table.

To insert a new row into the dependents table.

INSERT INTO dependents (
	first_name,
	last_name,
	relationship,
	employee_id
)
VALUES
	(
		'Dustin',
		'Johnson',
		'Child',
		178
	);Code language: SQL (Structured Query Language) (sql)

Try It

We did not use the department_id column in the INSERT statement because the dependent_id column is an auto-increment column, therefore, the database system uses the next integer number as the default value when you insert a new row.

The employee_id column is a foreign key that links the dependents table to the  employees table. Before adding the new rows, the database system checks if the value 178 exists in the employee_id column of the  employees table to make sure that the foreign key constraint is not violated.

If the row is inserted successfully, the database system returned the number of the affected rows.

Affected rows: 1Code language: SQL (Structured Query Language) (sql)

You can check whether the row has been inserted successfully or not by using the following SELECT statement.

SELECT 
    *
FROM
    dependents
WHERE
    employee_id = 178;Code language: SQL (Structured Query Language) (sql)

Try It

Insert multiple rows into a table

To insert multiple rows using a single INSERT statement, you use the following construct:

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

For example, to insert two rows into the dependents table, you use the following query.

INSERT INTO dependents (
	first_name,
	last_name,
	relationship,
	employee_id
)
VALUES
	(
		'Cameron',
		'Bell',
		'Child',
		192
	),
	(
		'Michelle',
		'Bell',
		'Child',
		192
	);Code language: SQL (Structured Query Language) (sql)

Try It

The database system returns 2 rows affected. You can verify the result using the following statement.

SELECT 
    *
FROM
    dependents
WHERE
    employee_id = 192;Code language: SQL (Structured Query Language) (sql)

Try It

Copy rows from other tables

You can use the INSERT statement to query data from one or more tables and insert it into another table as follows:

INSERT INTO table1 (column1, column2) 
SELECT
	column1,
	column2
FROM
	table2
WHERE
	condition1;Code language: SQL (Structured Query Language) (sql)

In this syntax, you use a SELECT which is called a subselect instead of the  VALUES clause . The subselect can contain the joins so that you can combine data from multiple tables. When executing the statement, the database system evaluates the subselect first before inserting data.

Suppose, you have a table named dependents_archive that has the same structure as the dependents table. The following statement copies all rows from the dependents table to the dependents_archive table.

INSERT INTO dependents_archive 
SELECT
	*
FROM
	dependents;Code language: SQL (Structured Query Language) (sql)

Try It

You can verify the insert operation by using the following statement.

SELECT
	*
FROM
	dependents_archive;Code language: SQL (Structured Query Language) (sql)

Try It

Now you should know how to use the SQL INSERT statement to insert one or more rows into a table.