Using SQL INSERT INTO statement for inserting table values

SQL INSERT INTO statement is used to insert the values in the database tables. Table values can be inserted into all the columns of the table or we can specify some columns where for which we want to insert the values.

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+

SQL INSERT INTO syntax and example for inserting the values in all the columns in table -

 INSERT INTO CUSTOMER VALUES (6,'NICOLE', 'KIDMAN', 50, 'CANADA');

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | CANADA    |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)

Using the above approach we cannot have to define some value to each column and we cannot leave anycolumn.

SQL INSERT INTO syntax and example for inserting the values in some specified columns in table -

Here we can specify the columns and their corresponding values in which we want to insert the values -

  INSERT INTO CUSTOMER (FNAME, LNAME, COUNTRY) VALUES ('GEORGE', 'CLOONEY', 'USA');

mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | CANADA    |
|  7 | GEORGE | CLOONEY | NULL | USA       |
+----+--------+---------+------+-----------+
7 rows in set (0.00 sec)

Here we have inserted values in FNAME, LNAME and COUNTRY columns only. Age column is NULL. ID column got updated automatically because we have defined it as primary key in our table , we will learn about primary keys in next few tutorials. Note : order of column names names and values must be same.