MySQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement is used to copy data from one table and insert it into another table. It allows you to transfer data between tables with or without specific conditions.

Basic Syntax for INSERT INTO SELECT

Copy all columns from one table to another:
```
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
```
- This will copy all columns from table1 and insert them into table2, based on the condition.
Copy specific columns from one table to another:
```
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
```
- This allows you to specify which columns from table1 should be inserted into table2.

MySQL INSERT INTO SELECT Examples

Example 1: Copy data from "Suppliers" into "Customers" (with NULL for missing columns)

If the Customers table has more columns than Suppliers, the unspecified columns in the Customers table will be set to NULL.
```
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
```
This query copies the SupplierName, City, and Country from the Suppliers table into the CustomerName, City, and Country columns of the Customers table.
Example 2: Copy all data from "Suppliers" into "Customers" (matching columns)

This query assumes that both tables have the same structure, and it copies all the columns.
```
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
```
This copies all relevant data from the Suppliers table to the Customers table, filling the respective columns.
Example 3: Copy only German suppliers into "Customers"

This query selects only suppliers from Germany and inserts them into the Customers table.
```
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
```
This inserts only those suppliers from Germany into the Customers table.

Important Notes

Matching Data Types: The data types of the columns in both the source and target tables must be compatible. Otherwise, an error will occur.
Target Table Records: Existing records in the target table will remain unaffected by the INSERT INTO SELECT statement, unless explicitly deleted or updated before or after the insert.
Conditional Inserts: You can use a WHERE clause to filter which rows should be inserted from the source table.