MySQL Tutorial
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>
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>
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)
MySQL Tutorial
Privacy Policy | Copyright2020 - All Rights Reserved. | Contact us
| Report website issues in Github
| Facebook page
| Google+ page