Insert into select mysql query basically copies the values from same table or another table and inserts it as a new row. You can copy all columns of the row or part of the row. Take a look at some examples in this post.
Inset Into Select Query Syntax
If you do not use Where clause, it will copy all the rows of the table and insert as new rows.
INSERT INTO [table_name] ([column1], [column2], [column3]) SELECT [column1], [column2], [column3] FROM [table_name] WHERE [column_name] = 'value';
Let us see some examples.
Inserting a row with the values of same table
Let us assume that you have a table called Products and you want to add the same product with same amount of quantity from a previous date as new entry but with the current date. You can simple do that with MySQL insert into query with the combination of select query like this.
INSERT INTO Products (pid, pname, qty, added_date) SELECT pid, pname, qty, CURDATE() FROM Products WHERE pid = '102';
Inserting a row with the values of another table
If you have two tables, for example Products and Cart and you want to copy a row from one table to another table. You can use this query to do that.
INSERT INTO Cart SELECT * FROM Products WHERE product_id = '3';
We are selecting a row from Products table by its product_id and inserting it to a table called Cart.
Copying a row from another table with arithmetic operation
While you are copying a row from another table, you can add, subtract or do any other arithmetic operations like below. This query selects a row from Products table and subtracts 4 from qty column then inserts a new row in Cart table.
INSERT INTO Cart (product_id, product_name, qty, added_date ) SELECT product_id, product_name, qty-4, added_date FROM Products WHERE product_id = '2';
Concatenating a text to the column while copying a row
You can append or prepend any text to one or more columns before copying it to a new table. The query below selects a row from Products table and appends the text “-with 5% discount” to the column product_name and inserts it to Cart table.
INSERT INTO Cart (product_id, product_name, qty, added_date ) SELECT product_id, CONCAT(product_name, '-with 5% discount'), qty-4, added_date FROM Products WHERE product_id = '1';
Inserting the sum of select rows
For example, if you have a Transactions table with product name, id and quantity and you want to sum all the quantities and insert the result into another table, use the query below.
INSERT INTO Sold_Report (pid, pname, qty, sold_date ) SELECT product_id, product_name , sum(qty), added_date FROM Transactions WHERE added_date = '2015-09-06' AND product_id = '1';
Note: the column names can be differed, but the column count should not be differed.
Copying all the rows from another table
Last but not least, In some cases you might want to copy all the rows from a table to another table, there are other ways to do this. But here is a method to do this using Insert into select query.
INSERT INTO Cart SELECT * FROM Products
Like mentioned before, this query will throw error if the column count doesn’t match.
Try these queries and leave your comments and feedback below.
Also take a look at the post “MySQL update join values from another table“