INSERT Statement in SQL Databases

Visual Guide to SQL INSERT: Explore the process of adding data to relational databases with our informative infographic.

For adding new data in the SQL database tables, you may use the INSERT INTO statement.

The SQL INSERT INTO statement allows you to enter the data in two ways.

By using the column names syntax

INSERT INTO table_name
(col1, col2, col3 ... )
VALUES
(val1, val2,val3 ... );

Without column names syntax

INSERT INTO table_name
VALUES
(val1, val2,val3 ... );
  • In the above syntax, you can see the usage of VALUES keywords. This is required in both ways as entering the data into tables.
  • You may enter one or more records while using the INSERT INTO statement.
Note: You may also use GUIs in different databases, like MS SQL Server for entering the data.

The example of using column names in INSERT INTO statement

As you enter data by using the column names of the table, you may use a different sequence than the actual physical structure of the table.

However, the column name sequence and the field values in parenthesis after the VALUES keyword must match.

The example below shows specifying the column names which is followed by using the VALUE keyword and the respective values of columns.

For that, I used our example table, sto_employees which keeps employees’ information in a departmental store.

The INSERT Query:

INSERT INTO sto_employees

(id, emp_name, emp_age, emp_salary, join_date, phone)

VALUES (9, 'Haynes', 25, 3300, '2018-03-20', '042-4521-458452');

SQL INSERT

  • All the column names are given and so are their respective values. In the second resultset, a row is added to the table.
  • The values contain int, varchar, and date data types. You can see that the int value is given without any quote.
  • The text values for the varchar type are enclosed in single quotes.
  • Similarly, for inserting date type, the value is enclosed in the single quotes.

INSERT INTO example without column names

This example enters a row into our example table without specifying the column names in the INSERT SQL statement.

As mentioned earlier, the sequence of values must be the same as the sequence of columns in the physical table. Otherwise, the INSERT query will try entering the record as provided in VALUES(), and data types of the provided value and column’s type may mismatch.

This may result in an error if the data is not matched. Even if the data type matches, the wrong values can be entered than expected (if you are not sure of the sequence).

See the example below for entering a record without providing the column names:

The INSERT query without column names:

INSERT INTO sto_employees

VALUES (10, 'Maria', 23, 3000, '2018-02-15', '321-1041-254241');

SQL INSERT no-columns

Note: You may execute above and below insert SQL queries in different databases like MySQL, MS SQL Server, Oracle, etc.

What if a column name value is not provided?

If your table has columns that allow NULL values, these can be skipped by using the INSERT statement.

The better practice for using INSERT INTO statement, in that case, will be using column names.

The INSERT query in SQL below shows entering a record with only specific column names. I omitted the phone and salary fields:

INSERT INTO sto_employees

(id, emp_name, emp_age, join_date)

VALUES (11, 'Hina', 35, '2018-03-20');

INSERT specific columns

In the table graphic, the fields whose values are not provided are showing NULL after entering the record into the employee’s table.

Add multiple records in a single INSERT query example

By writing a single INSERT INTO query, you may enter multiple records into the specified table.

For adding multiple rows, write the query of INSERT INTO till values (with or without column names), enclose the set of values in parenthesis for records, and separate those by commas.

See a demonstration below for entering multiple records by a single query:

INSERT INTO sto_employees

(id, emp_name, emp_age, join_date)

VALUES

(12, 'Raina', 27, '2018-04-20'),

(13, 'Jellani', 45, '2018-01-20'),

(14, 'Reema', 34, '2018-02-20');

The resultset after entering data:

SQL INSERT multiple

The graphic above shows that three records are added.

Inserting data from another table by INSERT INTO SELECT

You may copy data from one table to another by using the INSERT command with a SELECT statement.

In that case, the data type of the columns in the source and target tables must match, otherwise, an error is raised.

For using the SELECT statement with INSERT, use the following syntax:

INSERT INTO target_table

(col1, col2, col3….)

SELECT respective_col1, respective_col2, respective_col3…

FROM source_table

[WHERE condition];
For copying complete data from one table to another, you may omit the WHERE clause.

A detailed guide is written about using the INSERT with a SELECT statement here.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!