MySQL insert into select query; Through this tutorial, i am going to show you how to use the MySQL INSERT INTO SELECT Query statement to insert data into a table from the result of a SELECT statement.
MySQL Insert Into with Select Query
MySQL INSERT INTO SELECT statement query is used to copies data from one table and inserts it into another table in same query.
Syntax of MySQL Insert Into with Select Query
The basic syntax of MySQL Insert Into with Select Query; as follows:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
Example of MySQL INSERT INTO SELECT this syntax, copy some columns(selected column only) from one table to the second table; as follows:
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;
Example 1 – MySQL INSERT INTO SELECT MySQL
The INSERT INTO SELECT statement query is very useful when you want to copy data from one table to another table; As follows:
CREATE TABLE vendors( id INT AUTO_INCREMENT, name VARCHAR(50) NOT NULL, phone VARCHAR(50), addressLine1 VARCHAR(50), addressLine2 VARCHAR(50), city VARCHAR(50), state VARCHAR(50), postalCode VARCHAR(50), country VARCHAR(50), customer_id INT PRIMARY KEY (id) );
To create a vendor table, you just need to execute the MySQL query.
Now, you need to include these customers in the Vendor table from the Customer table. The following MySQL statement copies “Vendors” to “Customers”.
INSERT INTO vendors ( name, phone, addressLine1, addressLine2, city, state, postalCode, country, customer_id ) SELECT name, phone, addressLine1, addressLine2, city, state , postalCode, country, customer_id FROM customers;
The following MySQL statement copies only the UK vendors into “Customers”.
INSERT INTO vendors ( name, phone, addressLine1, addressLine2, city, state, postalCode, country, customer_id ) SELECT name, phone, addressLine1, addressLine2, city, state , postalCode, country, customer_id FROM customers WHERE country = 'UK';
After inserting data using the MySQL INSERT INTO SELECT statement query, the following query returns data from the vendors table : –
SELECT * FROM vendors;
Be First to Comment