Hit enter after type your search item

The INSERT statement in SQL databases

For populating new data into the SQL database tables, you may use the INSERT INTO statement. The SQL INSERT INTO statement allows you entering 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 as 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 entering data by using column names of the table, you may use 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 that keep employees information in a departmental store.

The INSERT Query:


SQL INSERT

You see, all the column names are given and so their respective values. In the second table, a row is added to the table.

The values contain int, varchar and date data types. You can see, the int value is given without any quote. The text values for varchar type are enclosed in single quotes. Similarly, for inserting date type, the value is enclosed in the single quotes.

The example INSERT INTO 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 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:


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 as 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 specific columns

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

Add multiple records in 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 single query:


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 INSERT command with SELECT statement. In that case, the data type of the columns in source and target tables must match, otherwise, an error is raised.

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


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 SELECT statement here.

This div height required for enabling the sticky sidebar