The INSERT INTO SELECT statement is a powerful command in SQL to copy the data by selecting the data from another table. This is mainly used for backups and bulk data operations without manually inserting the record into the table. In this blog, let’s explore different methods of inserting the values into the table.
Table of Contents:
Methods for Inserting the Data into the Table
Let us consider an Employee and High earners table and use it as an example for all the examples
Now, Let’s create an employee table
--Create an Employee table
CREATE TABLE Employees (
Employee_id INT PRIMARY KEY,
Employee_dept NVARCHAR(50),
Salary DECIMAL(10,2)
);
#The second High earners table can be created by
CREATE TABLE High_earners (
Employee_id INT PRIMARY KEY,
Salary DECIMAL(10,2)
);
Let us insert some values into the Employee table and the High_Earners table can be empty.
INSERT INTO Employees (Employee_id, Employee_dept, Salary)
VALUES
(121, 'BDT', 50000),
(122, 'TRA', 60000),
(123, 'TCW', 65000),
(124, 'BDT', 30000);
--To display the Employee table
Select * from Employees;
This is how the employee table looks when created and inserted with the values.
There are multiple ways to insert the data into a table in SQL. Below are the most commonly used methods:
Method 1: INSERT into SELECT Statement Without Using WHERE Clause in SQL
This method often allows us to insert data from one table into another, which would be used for data migration or transformation.
Syntax:
INSERT INTO target_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table
Example:
INSERT INTO high_earners (Employee_id, salary)
SELECT Employee_id, salary
FROM employees
-- To display the high_earners table
Select * from high_earners ;
Output:
Explanation: This INSERT INTO command inserts the Employee_id and salary into the high_earners table by simply copying the data from the Employees table to the High_earners table.
Method 2: INSERT a Single Row in SQL
In SQL, the “ Insert into ” statement allows adding a single row of data into a specified table, ensuring that values align with defined columns and data types.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...) ;
Example:
INSERT INTO employees (Employee_id,Employee_dept,Salary)
VALUES (125, 'TRA',52500);
--To display the employee table
Select * from employees;
Output: After inserting the single row, the employee table will look like this:
Explanation: This INSERT INTO query adds a new row to the employee table whose employee_id is 125.
Method 3: INSERT Multiple Rows in SQL
In SQL, INSERT multiple rows enables inserting multiple records into a table in a single execution by reducing the query overhead and improving performance.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1a, value2a, value3a, ...),
(value1b, value2b, value3b, ...),
(value1c, value2c, value3c, ...);
Example: Consider the already existing table called employees
Now let us add multiple rows to the employee’s table
INSERT INTO employee (Employee_id,Employee_dept,Salary)
VALUES
(126, 'BDT', 26000),
(127, 'TCW', 32000),
(128, 'TCW', 41000);
Output: After inserting the multiple rows, the employee table will look like this:
Explanation: By giving the multiple values in an INSERT INTO statement, those records are inserted into the Employee table.
Method 4: INSERT Data Without Specific Columns in SQL
If you’re inserting values of all the columns in the exact order, then they are defined in the table. However, the values must follow the table’s column order and match their data type.
Syntax:
INSERT INTO table_name
VALUES (value1, value2, value3, ...) ;
Example:
Consider the already existing table called employees
INSERT INTO employee
VALUES (129, ' BDT ',23000);
Output:
Explanation: In this INSERT INTO command, the field names are not mentioned, but the values are given in the correct order, which then inserts the given record into the employee table.
Method 5: INSERT with WHERE Clause in SQL
INSERT with WHERE Clause allows inserting specific rows from one table (Source_table) into another table (target_table) based on the condition. Used to insert specific employees into the table based on the condition.
Syntax:
INSERT INTO target_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table
WHERE condition;
Example:
Let us insert data into the high_earners table for employees whose salary is greater than 50,000
INSERT INTO High_earners (Employee_id, Salary)
SELECT Employee_id, Salary
FROM Employees
WHERE Salary > 50000;
-- Display the High_earners table
SELECT * FROM High_earners;
Output:
Explanation: This WHERE Salary > 50,000 statement fetches only those records that are inserted into the high_earners table using the INSERT INTO.
Method 6: INSERT with an Aggregate Function in SQL
We can use aggregate functions like AVG(), SUM(), etc., to manipulate the data before inserting it into the High_earners table.
Syntax:
INSERT INTO target_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table
WHERE column3 > (SELECT AGGREGATE_FUNCTION(column3) FROM source_table);
Example: In this example, let’s insert the employees whose salary is greater than the average salary.
INSERT INTO High_earners (Employee_id, Salary)
SELECT Employee_id, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
-- Display the High_earners table
SELECT * FROM High_earners;
Output:
Explanation: This WHERE Salary statement filters employees whose salary is greater than the average salary in the employees table. Only those records are inserted into the employee table.
Method 7: INSERT with Output Clause in SQL
The output clause in SQL can be used to capture the inserted rows, which we can use for further processing.
Syntax:
INSERT INTO target_table (column1, column2, column3)
OUTPUT INSERTED.column1, INSERTED.column2, INSERTED.column3
SELECT column1, column2, column3 FROM source_table WHERE condition;
Example: Let us insert employees with high salaries and output their details
INSERT INTO High_earners (Employee_id, Salary)
OUTPUT INSERTED.Employee_id, INSERTED.Salary
SELECT Employee_id, Salary
FROM Employees
WHERE Salary > 21000;
-- Display the High_earners table
SELECT * FROM High_earners;
Output:
Explanation: The records with a salary greater than 21,000 are fetched from the employee table and only those records are inserted into the high_earners table.
Method 8: INSERT INTO a Temporary Table in SQL
This method demonstrates inserting data into a temporary table for further processing.
Syntax:
CREATE TABLE TempTable (
column1 datatype,
column2 datatype,
column3 datatype
);
INSERT INTO TempTable
SELECT column1, column2, column3 FROM source_table WHERE condition;
SELECT * FROM TempTable;
Example: In this example, a temporary table TempHighEarners is used to hold the employees with high salaries.
--Creating a temporary table
CREATE TABLE TempHighEarners (
Employee_id INT,
Salary DECIMAL(10,2)
);
-- Inserting data into the temporary table
INSERT INTO TempHighEarners (Employee_id, Salary)
SELECT Employee_id, Salary
FROM Employees
WHERE Salary > 50000;
-- Select from the temporary table to verify
SELECT * FROM TempHighEarners;
Output:
Explanation: The records with a salary greater than 50,000 are fetched from the employee table and only those records are inserted into the Temporary High Earners table.
Real-world Use cases
1. Archiving Old Data: It helps to move records of the employees who left the company to an archive table.
Example:
--Create an employee table
CREATE TABLE Employees (
Employee_id INT PRIMARY KEY,
Employee_name VARCHAR(100),
Employee_dept VARCHAR(50),
Exit_date DATE
);
--Insert some values into it
INSERT INTO Employees (Employee_id, Employee_name, Employee_dept, Exit_date)
VALUES
(101, 'Alice Johnson', 'IT', '2023-06-15'),
(102, 'Bob Smith', 'HR', NULL),
(103, 'Charlie Brown', 'Finance', '2024-01-10'),
(104, 'David Miller', 'Marketing', NULL),
(105, 'Emma Watson', 'Sales', '2023-12-05');
--Create the employees_archive table
CREATE TABLE Employees_Archive (
Employee_id INT PRIMARY KEY,
Employee_name VARCHAR(100),
Employee_dept VARCHAR(50),
Exit_date DATE
);
--Inserting the details of the Employees who left recently into the Employee_Archive table
INSERT INTO Employees_Archive (Employee_id, Employee_name, Employee_dept, Exit_date)
SELECT Employee_id, Employee_name, Employee_dept, Exit_date
FROM Employees
WHERE Exit_date IS NOT NULL;
--To display the Employees_Archive
Select * from Employees_Archive;
Output:
Explanation: The WHERE Clause retrieves the employees who left the company with a non-NULL Exit_date. The outer select statement retrieves all records from the Employees_Archeive table.
2. Creating a BackUp before deleting data: Before deleting the inactive users, we can store them in a backup table.
Example:
--Create a User table
CREATE TABLE Users (
User_ID INT PRIMARY KEY,
Username VARCHAR(100),
Last_Login DATE
);
--Insert sample data into the user table
INSERT INTO Users (User_ID, Username, Last_Login)
VALUES
(1, 'Alice', '2023-02-10'),
(2, 'Bob', '2024-01-15'),
(3, 'Charlie', '2022-10-05'),
(4, 'David', '2023-07-20'),
(5, 'Emma', '2021-12-01');
--Create Inactive User’s table
CREATE TABLE Inactive_Users (
User_ID INT PRIMARY KEY,
Username VARCHAR(100),
Last_Login DATE
);
--Insert inactive users into the Inactive_users table
INSERT INTO Inactive_Users (User_ID, Username, Last_Login)
SELECT User_ID, Username, Last_Login
FROM Users
WHERE Last_Login < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
--To display the Inactive users
SELECT * FROM Inactive_Users;
Output:
Explanation: This “WHERE Last_Login < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)” filters out those who haven’t logged in over a year. The CURDATE() gets today’s date. The “DATE_SUB(CURDATE(), INTERVAL 1 YEAR)” queries subtract 1 year from today’s date.
3. Populating a reporting table: This can be used for inserting the summarized sales data into a reporting table.
Example:
--Create the sales table
CREATE TABLE Sales (
Sale_ID INT PRIMARY KEY,
Product_Name VARCHAR(100),
Quantity_Sold INT,
Sale_Amount DECIMAL(10,2),
Sale_Date DATE
);
--Insert sample data into the Sales table
INSERT INTO Sales (Sale_ID, Product_Name, Quantity_Sold, Sale_Amount, Sale_Date)
VALUES
(1, 'Laptop', 2, 2000.00, '2024-02-01'),
(2, 'Smartphone', 3, 1500.00, '2024-02-02'),
(3, 'Laptop', 1, 1000.00, '2024-02-03'),
(4, 'Tablet', 5, 2500.00, '2024-02-04'),
(5, 'Smartphone', 2, 1000.00, '2024-02-05');
--Create the sales_report table
CREATE TABLE Sales_Report (
Product_Name VARCHAR(100) PRIMARY KEY,
Total_Quantity_Sold INT,
Total_Sales DECIMAL(10,2)
);
--Insert the data into the Sales_report table
INSERT INTO Sales_Report (Product_Name, Total_Quantity_Sold, Total_Sales)
SELECT Product_Name, SUM(Quantity_Sold), SUM(Sale_Amount)
FROM Sales
GROUP BY Product_Name;
--To display the Sales_report table
SELECT * FROM Sales_Report;
Output:
Explanation: The SELECT query retrieves data from the Sales table while performing the calculations on Quantity_Sold and Sale_Amount. Then the GROUP BY results by Product_Name so that each product gets its row.
Difference between INSERT INTO VALUES(…) and INSERT INTO SELECT
| INSERT INTO VALUES(…) | INSERT INTO SELECT |
| Manually inserts specific values into a table | Copies data from another table using the SELECT statement |
| Bests for inserting small datasets with known values | Best for transferring large datasets dynamically |
| Slower for large datasets as values must be specified manually. | More efficient for bulk inserts as it retrieves data directly. |
| Can insert completely new values. | Can only insert data that exists in another table. |
Conclusion
We have summarized that SQL offers a variety of ways to insert data into tables efficiently. These methods include inserting values directly, inserting multiple rows, or using more advanced techniques like inserting based on conditions, aggregate functions, or using temporary tables for intermediate processing. Understanding all these approaches helps you to effectively INSERT the values using SELECT statements in SQL.
FAQs
1. What does INSERT INTO ... SELECT ... do?
It inserts data from one table into another by selecting specific columns instead of manually specifying values.
2. Can I insert data from multiple rows using SELECT?
Yes, SELECT can retrieve multiple rows, and all of them will be inserted into the target table.
3. Do the column types need to match in both tables?
Yes, the selected columns must have compatible data types with the columns in the target table to avoid errors.
4. What is the purpose of using INSERT INTO ... SELECT ... FROM?
It allows inserting data from one table into another without manually specifying values.
5. Can we use INSERT INTO ... SELECT ... FROM with a WHERE clause?
Yes, adding a WHERE clause filters the data being inserted, allowing selective row insertion.