SQL Insert Multiple rows From Select

In this article, we are going to discuss how to sql insert multiple rows from select. If you know, the INSERT INTO command in sql is used to add data(single row and multiple rows) in the table.

--create

CREATE TABLE Employee(
name CHAR(20),
age INT,
dep CHAR(10)
);

--insert
INSERT INTO Employee VALUES ('Clark',25, 'Sales');
INSERT INTO Employee VALUES ('Dave', 32,'Accounting');
INSERT INTO Employee VALUES ( 'Ava', 28, 'Sales');

--fetch
SELECT * FROM Employee;

Output:

In the above Employee table, the data is given for four columns like ID, name, and Department. Here we use INSERT INTO statement while adding each row value. In response to using this method, we can pass multiple row values ​​even in a single INSERT INTO statement. For that, follow the given below syntax:

CREATE TABLE Sales(
    Name varCHAR(20),
    Age INT,
    Dep varCHAR(10)
    );
    
    INSERT INTO Sales(Name, Age, Dep)
     SELECT name, age, dep
     FROM Employee
     WHERE dep = 'Sales';
     
     SELECT * FROM Sales;

Output:

1. Here, we have created another table “Sales” and created the same column Name, Age, Dep.
2. I want to copy the data from the “Employee” table which the employee has the sales department and paste it into the “Sales” table.
3. To do that, we need to use INSET INTO, SELECT FROM, and WHERE statements. INSET INTO is used to add the data into the “Sales” table, SELECT FROM is used to select the data in the “Employee” table and WHERE is used to check the condition dept = ‘sales’.
4. Finally, we have printed the Sales table.
5.  See, the output table Sales department employee is added into Sales table successfully