SQL Insert statement example explanation

Posted by Xyox on Wed, 19 Jan 2022 10:21:54 +0100

In this tutorial, we will learn how to use SQL INSERT statements to INSERT data into tables.

1. Introduction to SQL insert statement

SQL provides an INSERT statement to INSERT one or more rows into a table. The INSERT statement is used to:

Insert a row into the table
Insert multiple rows into the table
Copy rows from one table to another.

We will look at each usage of the INSERT statement in the following sections.

For more tutorials, visit http://www.manongzj.com

2. Insert a row into the table

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

INSERT INTO table1 (column1, column2,...)
VALUES
    (value1, value2,...);

When inserting a new row into a table, you should pay attention to the following points:

First, the number of values must be the same as the number of columns. In addition, columns and values must correspond because the database system will match them by their relative position in the list.
Second, before adding a new row, the database system checks all integrity constraints, for example, Foreign key constraint，Primary key constraint，Check constraints And non null constraints. If one of these constraints is violated, the database system issues an error and terminates the statement without inserting any new rows into the table.

If the sequence of values matches the order of the columns in the table, you do not need to specify columns. See the following INSERT statement, which omits the list of columns in the INSERT INTO clause.

INSERT INTO table1
VALUES
    (value1, value2,...);

However, this is not a good practice.

If a column and its value are not specified in the INSERT statement when a new row is inserted, the column adopts the default value specified in the table structure. The default value can be 0, the next integer value in the sequence, the current time, NULL value, etc. See the following statement:

INSERT INTO (column1, column3)
VALUES
    (column1, column3);

In this syntax, column2 takes the default value.

2. Insert a row of records in the table

We will use Sample database To demonstrate how to insert a row into the table.

To insert a new row in the dependencies table. Refer to the following statement-

INSERT INTO dependents (
    first_name,
    last_name,
    relationship,
    employee_id
)VALUES('Max','Su','Child',176);

We did not use department in the INSERT statement_ ID column, because dependent_ The ID column is an auto increment column, so when a new row is inserted, the database system uses the next integer as the default value.

employee_ The ID column is the foreign key that links the dependencies table to the employees table. Before adding a new row, the database system checks the employees of the employees table_ Whether there is a value in the ID column: 176 to ensure that the foreign key constraint is not violated.

If rows are successfully inserted, the database system returns the number of rows affected.

You can use the following SELECT statement to check whether the row was successfully inserted.

SELECT 
    *
FROM
    dependents
WHERE
    employee_id = 176;

Execute the above query statement and get the following results-

+--------------+------------+-----------+--------------+-------------+
| dependent_id | first_name | last_name | relationship | employee_id |
+--------------+------------+-----------+--------------+-------------+
|           31 | Max        | Su        | Child        |         176 |
+--------------+------------+-----------+--------------+-------------+
1 rows in set

3. Insert multiple rows of records into the table

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

INSERT INTO table1
VALUES
    (value1, value2,...),
    (value1, value2,...),
    (value1, value2,...),
    ...;

For example, to insert two rows in the dependencies table, use the following query.

INSERT INTO dependents (
    first_name,
    last_name,
    relationship,
    employee_id
)
VALUES
    (
        'Avg',
        'Lee',
        'Child',
        192
    ),
    (
        'Michelle',
        'Lee',
        'Child',
        192
    );

The database system returns 2 rows, which are affected. You can use the following statement to verify the results.

SELECT 
    *
FROM
    dependents
WHERE
    employee_id = 192;

4. Copy row records 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, select (called sub selection) is used instead of the VALUES clause. Sub selections can contain joins so that data from multiple tables can be combined. When executing a statement, the database system first evaluates the sub selection before inserting data.

Let's say there's one called dependencies_ The structure of the archive table is the same as that of the dependencies table. The following statement copies all rows in the dependencies table to dependencies_ In the archive table.

INSERT INTO dependents_archive 
SELECT
    *
FROM
    dependents;

You can use the following statement to verify that the insert operation was successful-

SELECT
    *
FROM
    dependents_archive;

Through the above example, we have learned how to INSERT one or more rows into a table using SQL INSERT statement.

Programmer Think