Learn how to use SQL INSERT INTO SELECT statement with the examples. Find out how to insert data by selecting other table using SQL INSERT INTO SELECT statement.
You have to specify column names of both the tables to which you want to insert data and fetch data. Combine the INSERT and SELECT statement together in one statement to insert data by selecting another table. You can insert specified column data or all the data from other table using the syntax given below
Also, apply the WHERE condition to get only the required data from another table. Using one table column to insert into another table column can insert multiple records instantly to the specified table.
How to Use SQL INSERT INTO SELECT Statement to insert data
There are two different syntaxes you can use to insert the values from one table to another table. You can insert values for the specified columns of a table from the specified column of another table from where you have to get the data.
Syntax1: Insert all Column Values From Another Table
|
1 2 3 |
INSERT INTO tablename2 SELECT * FROM tablename1 WHERE condition; |
The above syntax inserts data from the old table using the WHERE condition. It inserts all the column data from the table specified in the SELECT statement to the table specified in the INSERT statement.
Syntax2: Insert Values from the Specified Columns
|
1 2 3 4 |
INSERT INTO tablename2 (column1, column2, column3,...columnN) SELECT column1, column2, column3,...columnN FROM tablename1 WHERE condition; |
If you want to insert values for specified columns, you have to use the above syntax. Specify the column names in the same sequence inside the INSERT and SELECT statement. If you will not specify the WHERE condition, the above syntax copy all the record for the specified columns.
Parameter Descriptions
| Sr.No | Parameter Name | Description |
|---|---|---|
| 1 | column1, column2, column3,…columnN | Enter the column names for both SELECT and INSERT statement to copy the column data from one table to another table. |
| 2 | tablename1 and tablename2 | tablename1 is the already developed table from where you want to get the data and insert into the tablename2. |
| 3 | condition | The parameter can use to specify the condition from which you will get only the required data from the table. |
|
1 2 3 |
INSERT INTO Employee SELECT * FROM Supplier WHERE Salary>25000; |
|
1 2 3 4 |
INSERT INTO Supplier (Name, Email, Designation) SELECT Name, Email, Designation FROM Employee WHERE Salary>25000; |
You must also learn.
Reference