09 - SQL Insert records into Table

Now that we have created database, table and also the structure of the table, it is time to enter data into the table. This is done using “INSERT INTO” statement in SQL. Insert Into creates new record(s) in a table. There are 3 ways to insert records into the table.

a. Basic INSERT INTO statement.

The syntax is as below:

INSERT INTO table_name

VALUES (value1, value2, value3,….valueN);

This statement will create only 1 record at a time. The data type specified in the values section should match with the data type provided while defining the table structure.

Example 1:

Insert records to table employee with below structure:

+----------------------------+------------------+--------+---------+----------+--------+
| Field             | Type            | Null     | Key     | Default | Extra |
+----------------------------+------------------+--------+---------+----------+--------+
| EMP_ID            | int(11)         | NO       | PRI      | NULL    |       |
| EMMP_NAME         | varchar(20)     | NO       |          | NULL    |       |
| AGEG              | int(11)         | NO       |          | NULL    |       |
| PHONE_NUM         | int(11)         | YES      |          | NULL    |       |
| DEPT_ID           | int(11)         | NO       | MUL      | NULL    |       |
+----------------------------+------------------+--------+---------+----------+--------+

 

 mysql> insert into employee

 -> values(1, "john",35,100233023,2);

Query OK, 1 row affected (0.06 sec)

Please note, if you forget to provide data for any of the column then there would be error.

 

mysql> insert into employee

    -> values("linda",30,1);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

After entering all the values you can check if the data is correct by using SELECT statement (details in next chapters).

mysql> SELECT * FROM EMPLOYEE;
+-------------------+---------------------------+------------------+----------------------------+-------------+
| EMP_ID     | EMMP_NAME     | AGEG     | PHONE_NUM     | DEPT_ID |
+-------------------+---------------------------+------------------+----------------------------+-------------+
|      1     | john          |   35     | 100233023     |       2 |
+-------------------+---------------------------+------------------+----------------------------+-------------+
1 row in set (0.08 sec) 

b. INSERT INTO by providing the column names.

The syntax is as below:

 

INSERT INTO table_name (column1, column2, column3,…columnN)

VALUES (value1, value2, value3,….valueN);

This statement will create only 1 record at a time.

Here we can skip only those columns which have been defined to support NULL values. NOT NULL columns if skipped will lead to error message.

Example 1:

Insert records into table employee.

mysql> insert into employee(emp_id,emmp_name,ageg,phone_num,dept_id)

    ->  values(2,"Linda",30,100234565,1);

Query OK, 1 row affected (0.05 sec)

The column names should be mentioned correctly else you will get an error message like in example below: 

mysql> insert into employee(emp_id,emp_name,ageg,phone_num,dept_id)

    -> values(2,"Linda",30,100234565,1);

ERROR 1054 (42S22): Unknown column 'emp_name' in 'field list'

One advantage of using column names in INSERT is that you need not remember the sequence of columns; you can provide the values as per the sequence mentioned in the INSERT statement like below:

Example 2:

Insert record into table employee without following the sequence of the table.

mysql> insert into employee(ageg,emp_id,phone_num,emmp_name,dept_id)

    -> values(40,3,122222344,"Max",3);

Query OK, 1 row affected (0.03 sec)

c. INSERT INTO a table using data from other table.

By this method, you can insert records from multiple tables. This is similar to the CREATE TABLE AS statement (refer CREATE TABLE section).

The syntax is as below:

INSERT INTO table_name1 (column1, column2, column3,…columnN)

                        SELECT col1, col2, col3,…colN from table_name2

                        [WHERE (condition)];

 This is useful, when the data of the table 1 is subset of table 2. It will insert multiple rows into the table.

Example 1:

Consider below is the list of employee 

+------------+--------------------+---------+--------------------+-------------------+
| EMP_ID | EMMP_NAME | AGEG | PHONE_NUM | DEPT_ID |
+------------+--------------------+---------+--------------------+-------------------+
|      1 | john      |   35     | 100233023     |       2    |
|      2 | Linda     |   30     | 100234565     |       1    |
|      3 | Max       |   40     | 122222344     |       3    |
|      4 | Will      |   40     |  12323424     |       3    |
|      5 | Michal    |   45     |  12323434     |       3    |
+------------+--------------------+---------+--------------------+--------------------+


Suppose we have to insert all the records with department_id = 3 to some other table for special processing then that can be done as below:

Assuming that you have already created the new table and the data types of new table are same as the old one.

mysql> insert into employee_deptid

    -> select * from employee

    -> where dept_id = 3;

Query OK, 3 rows affected (0.17 sec)

Records: 3  Duplicates: 0  Warnings: 0

Now you can check the contents of new table: 

mysql> select * from employee_deptid;
+--------+-------------------+------+-----------------+-----------+
| emp_id | emp_name | age  | phone_num | dept_id |
+--------+-------------------+------+-----------------+-----------+
|      3 | Max      |   40 | 122222344 |     3   |
|      4 | Will     |   40 |  12323424 |     3   |
|      5 | Michal   |   45 |  12323434 |     3   |
+--------+-------------------+------+-----------------+-----------+
3 rows in set (0.05 sec)

Question:

For a vendor table with columns Vendor_id, Vendor_Name and City, insert vendor_id 1001 and vendor_name as TOPCO.

Solution:

First create table as below:

 

         CREATE TABLE VENDOR_LIST
         (VENDOR_ID INT(4) NOT NULL,
        VENDOR_NAME VARCHAR(20) NOT NULL,
        VENDOR_CITY VARCHAR(10)
        PRIMARY KEY (VENDOR_ID));

 

Then use the INSERT statement. 

INSERT INTO VENDOR_LIST (VENDOR_ID, VENDOR_NAME)
VALUES (1001,”TOPCO”);

Like us on Facebook