Insert into statement SQL

In the previous chapter, we learnt how to create tables by using the Create table command. Now as a table is created in SQL database (MySQL, SQL server etc.) it is time to learn how to insert data into tables by using SQL commands.

The insert into statement is used to enter or insert data into the database tables. This chapter will explain how to use the insert statement with examples but first of all, let us look at its syntax.

Syntax of Insert into SQL

The general syntax of SQL insert statement is:

Insert into table_name (

                Col1,

                Col2,

                Col3,

                Col4

                …..

                …..

                Colx)

Values (

                Col1_value,

                Col2_value,

                Col3_value,

                Col4_value,

                …..

                …..

                Colx_value )

);

Where the insert statement is followed by the name of the table where you are intended to insert data. After table name, fields or column names of the table in parenthesis are given.

Lastly, values of those columns or fields in the same sequence as the column names are given in the insert statement.

This is one way of using the insert into statement. The other way of using insert SQL statement is as follows:

Insert into table_name Values (

                Col1_value,

                Col2_value,

                Col3_value,

                Col4_value,

                …..

                …..

                Colx_value )

);

That is, you can also provide only the values without giving the column names in the insert into statement.

If you intend to use this way, without column names, then ensure that you are using the same order as columns in the table. Or else you will end up in confusion or may be an error if data types are not matched.

As we have gone through the syntax of insert SQL statement, now let us use it with examples.

SQL insert into statement with column names example

The following example shows how to use the insert statement. For the examples of using the insert into statement, we will use the same table that we created in the create table chapter. Just to remind you, this is the table that we created in create table chapter:

Now as we have a table tbl_employee with five columns (emp_id, emp_name, emp_age, emp_salary and joining_date). Now let us insert rows into tbl_employee by using the insert into statement by specifying column names.

Insert into with column names example

The output should be

(1 row(s) affected) OR 1 row inserted. (Query took 0.0310 sec)

(depending on your SQL database).

Note that, as column names are provided, the order of specifying columns in the above SQL query can be different to column names in the table physically. However, the values in insert statement must be according to the order of columns in the query.

See another example below to understand that:

As you can see, the order of columns is changed in the above query, joining_date appears before emp_name. And the order of values is also according to column names. However, in actual table, tbl_employee, the order of the column is the same.

insert statement without column names example

In the following example, we will insert a row without specifying the column names. As mentioned earlier, the order of values must correspond to column names of the table in the insert statement.

Note that, if you will not keep the same order as in table (tbl_employee) either it will generate an error if data types do not match. Or it may end up at a confusion if the data types of columns match by chance.

Insert multiple rows in single query

We can also insert multiple rows by using insert SQL statement. See the example below.

As you can see, each row to be inserted should be separated by ‘,’ to enter multiple rows using insert statement.

You can also use insert into with select statement to enter the data, which is explained in its own chapter. See link below to learn it.

Also see SQL insert with select | The update statement