Insert rows in mysql table

Insert rows in mysql table

SQL INSERT INTO command is used to insert the data into mysql table.

INSERT INTO table_name ( column1, column22,..... ) VALUES ( value1, value2,..... );

Sample insert sql command to insert the employee rows in mysql table 'tblemployee'.

INSERT INTO tblemployee (emp_name, dept_id, salary) VALUES ('emp1', 10, 10000);

In above mysql command, column 'emp_id' value is not provided and automatically computed since used AUTO_INCREMENT to the emp_id column at the time of table creation.

mysql> INSERT INTO tblemployee(emp_name, dept_id, salary) VALUES ('emp1', 10, 10000)
    -> ;
Query OK, 1 row affected (1.04 sec)

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
+--------+----------+---------+--------+
1 row in set (0.01 sec)

mysql>

we can also provide the column 'emp_id' in mysql table 'tblemployee' since used AUTO_INCREMENT at the time of table creation.

mysql> INSERT INTO tblemployee(emp_id, emp_name, dept_id, salary) VALUES (1, 'emp1', 10, 10000)
    -> ;
Query OK, 1 row affected (1.04 sec)

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
+--------+----------+---------+--------+
1 row in set (0.01 sec)

mysql>

Insert multiple rows in mysql table

Sample insert sql command to insert the multiple employee rows in mysql table 'tblemployee'.

Insert multiple rows syntax:

INSERT INTO  (column1, column2,....) 
VALUES 
(value1, value2,....),
(value1, value2,....),
((value1, value2,....)

Let us see how to insert multiple rows in mysql table 'tblemployee'.

mysql> INSERT INTO tblemployee (emp_name, dept_id, salary)
    -> VALUES
    -> ('emp2', 11, 20000),
    -> ('emp3', 10, 15000),
    -> ('emp4', 11, 12000)
    -> ;
Query OK, 3 rows affected (0.45 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql>

Insert the multiple rows based on another mysql table

mysql command syntax,

INSERT INTO  (column1, column2, ...)
  SELECT column1, column2
  FROM  

Lets create a new table as same schema of table 'tblemployee' and insert the multiple rows in new table 'tblemployee1' based on existing table 'tblemployee'.

mysql>  create table tblemployee1(
    ->     emp_id INT NOT NULL AUTO_INCREMENT,
    ->     emp_name VARCHAR(100) NOT NULL,
    ->     dept_id INT,
    ->     salary INT,
    ->     PRIMARY KEY ( emp_id )
    ->     );
Query OK, 0 rows affected (0.69 sec)

mysql> select * from tblemployee1;
Empty set (0.01 sec)

mysql> insert into tblemployee1 (emp_id, emp_name, dept_id, salary) select * from tblemployee;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tblemployee1;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

« Previous Next »