SQL INSERT Statement – Inserting Table Records

We are able to insert records into a database table using a SQL INSERT statement. Alternatively, we can utilize the SELECT statement when inserting records. The following syntax shows how to do a basic insert.

INSERT INTO <table_name> VALUES(<all_field_values>); — SQL insert statement not specifying columns

We do not have to specify the columns if we include all column values in our values statement. These values are comma separated.

Opposite of that, if we do not want to insert all values into a table then we can specify them.

INSERT INTO <table_name>(column_1, column_2, column_n…) VALUES(value_1, value_2, value_n…); — Specifying table columns

We should always make sure that we include any NOT NULL fields when executing an insert statement. As a result, an insert query will not execute if a table column is specified as NOT NULL.

As an example, we can show an insert statement for an Employee table.

Inserting Multiple Records

Using the SQL INSERT statement is fine when inserting only a few records. Sometimes we need to insert larger datasets. For that reason, we may want to consider other options such as inserting records using a SELECT statement. In that case we’d have to assume data is already sitting in another table.

Alternatively, we could just write multiple insert statements. The below syntax shows us that we can separate out our value sets in a single insert.

INSERT INTO <table_name>(column_1, column_2, column_n…)
VALUES
(set_1_value_1, set_1_value_2, set_1_value_n…),
(set_2_value_1, set_2_value_2, set_2_value_n…),
(set_n_value_1, set_n_value_2, set_n_value_n…); — Specifying multiple records in single insert

Similarly, we can see a practical example that shows an insert statement with multiple employee records.

Final Thoughts

The SQL INSERT statement is considered part of the DML language since we are adding data into a table. Being able to add records into a table is just as important as being able to remove records from a table. As a result, we should be careful when inserting records into a table as to not duplicate records or input wrong data.

Lastly, if data we’re looking to insert is stored elsewhere in the database we could probably write a SQL query that creates SQL inserts. Those statements can then be stored in a script and run on their own. At the end of the day it depends on the need.