SQL INSERT Statement

Summary: in this tutorial, you’ll learn how to use the SQL INSERT statement to insert a row into a table.

Introduction to the SQL INSERT statement #

In SQL, the INSERT statement allows you to insert one or more rows into a table.

Here’s the syntax of the INSERT statement:

INSERT INTO table_name(column1, column2, column3)
VALUES(value1, value2, value3);Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the table to which you want to add data in the INSERT INTO clause.
Second, define a comma-separated list of columns of the table within parentheses after the table name.
Third, provide a comma-separated list of values for the corresponding columns in the VALUES clause. The number of values in the VALUES clause must be the same as the number of columns in the INSERT INTO clause.

If you don’t provide a column in the INSERT statement, it will take a default value.

The default value of a column is NULL or a value defined by a DEFAULT constraint.

SQL INSERT statement examples #

We’ll use the dependents table in the sample database:

The following INSERT statement inserts a new row into the 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

The INSERT statement inserts a new row with the values in the first_name, last_name, relationship, and employee_id columns.

The INSERT statement does not include the dependent_id column because it is an identity column; the database system automatically generates an integer for it.

To verify the insert, you can query data from the dependents table using the following statement:

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

Try it

 dependent_id | first_name | last_name | relationship | employee_id
--------------+------------+-----------+--------------+-------------
           31 | Dustin     | Johnson   | Child        |         178Code language: plaintext (plaintext)

Insert multiple rows into a table #

To insert multiple rows using a single INSERT statement, you use place multiple row values in the VALUES clause as follows:

INSERT INTO
  table_name (column1, column2, column3)
VALUES
  (value11, value12, value13),
  (value21, value22, value23),
  (value31, value32, value33);Code language: SQL (Structured Query Language) (sql)

For example, the following INSERT statement inserts two rows into the dependents table:

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

To verify the inserts, you can query data from the dependents table:

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

Try it

Output:

 dependent_id | first_name | last_name | relationship | employee_id
--------------+------------+-----------+--------------+-------------
           32 | Cameron    | Bell      | Child        |         192
           33 | Michelle   | Bell      | Child        |         192Code language: plaintext (plaintext)

Summary #

Use the INSERT statement to insert a new row into a table.
Use multiple row in the VALUES clause to insert multiple rows into a table once.

Quiz #

Databases #

Was this tutorial helpful ?