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
a statement that allows you to insert one or more rows into a table. The INSERT
the statement allows you to:
- Insert a single row into a table
- Insert multiple rows into a table
- 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,...);
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 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
a statement that omits the column list in the INSERT INTO
clause.
INSERT INTO table1
VALUES
(value1, value2,...);
However, this is not considering 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);
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
);
We did not use the department_id
column in the INSERT
statement because the dependent_id
the 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
the 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: 1
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;
Insert multiple rows into a table
To insert multiple rows using a single INSERT
the statement, you use the following construct:
INSERT INTO table1
VALUES
(value1, value2,...),
(value1, value2,...),
(value1, value2,...),
...;
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
);
The database system returns 2 rows affected. You can verify the result using the following statement.
SELECT
*
FROM
dependents
WHERE
employee_id = 192;
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;
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;
You can verify the insert operation by using the following statement.
SELECT
*
FROM
dependents_archive;
Now you should know how to use the SQL INSERT statement to insert one or more rows into a table.
0 Comments
CAN FEEDBACK
Emoji