In This Tutorial:
In the previous chapter, we learned 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 the 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 SQL Insert into command
The general syntax of the SQL insert statement is:
Insert into table_name (
Where the insert statement is followed by the name of the table where you are intended to insert data. After the table name, fields or column names of the table in parenthesis are given.
Lastly, the values of those columns or fields in the same sequence as the column names are given in the insert statement are provided.
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 (
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 physically. Otherwise, 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 the insert statement, now let us use it with examples.
The 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:
Create table tbl_employee
PRIMARY KEY (emp_id)
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
insert into tbl_employee (emp_id, emp_name, emp_age, emp_salary, joining_date)
Values (001, 'Mike', 35, 5000.50, '2008/01/01')
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:
insert into tbl_employee (emp_id, joining_date, emp_age, emp_salary, emp_name)
Values (002, '2008/05/01', 30, 4500.50, 'Michale')
As you can see, the order of columns is changed in the above query; the joining_date appears before emp_name while 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 the values must correspond to the column names of the table in the insert statement.
insert into tbl_employee Values (003, 'Jimmy', 27, 3000.00, '2009/05/03')
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 may also insert multiple rows by using the insert statement. See the example below.
insert into tbl_employee Values
(004, 'Jimmy', 27, 3000.00, '2009/05/03'),
(005, 'Jimmy', 27, 3000.00, '2009/05/03'),
(006, 'Jimmy', 27, 3000.00, '2009/05/03')
As you can see, each row to be inserted should be separated by ‘,’ to enter multiple rows using the 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 for learning it.
Related reading SQL insert with select | The update statement