SQL INSERT INTO SELECT
Introduction to SQL INSERT INTO SELECT STATEMENT
SQL (Structured Query Language), data manipulation is a fundamental task. One of the most powerful and versatile commands at your disposal is the INSERT INTO SELECT statement.
This SQL feature allows you to copy data from one table and insert it into another, all while customizing the data in the process.
In this page, we’ll discuss about the intricacies of SQL INSERT INTO SELECT statements, empowering you to harness its full potential for your data management needs.
Understanding the SQL INSERT INTO SELECT Statement
SQL’s INSERT INTO SELECT statement is a potent tool for data manipulation. It enables you to insert rows into a target table by selecting data from one or more source tables. This single command can efficiently perform various tasks, such as data migration, consolidation, and transformation, making it a cornerstone of SQL proficiency.
The INSERT INTO SELECT statement, often referred to as a subquery, is a fundamental SQL operation that allows you to insert data into one table based on the result set of a SELECT query.
SQL INSERT INTO SELECT STATEMENT SYNTAX :
INSERT INTO target_table (column1, column2, ...) SELECT source_column1, source_column2, ... FROM source_table WHERE condition;
target_table: The table into which you want to insert data.(column1, column2, ...): Optional. Specifies the columns in the target table where data will be inserted. If omitted, data will be inserted into all columns in the same order as in the source query.SELECT source_column1, source_column2, ...: The SELECT query that retrieves the data you want to insert.FROM source_table: Specifies the source table from which data is selected.WHEREcondition: Optional. Allows you to filter the rows to be inserted based on a specified condition.
Example: Inserting Data with SQL INSERT INTO SELECT
The SQL INSERT INTO SELECT statement:
-- Copy all rows from 'source_data' to 'destination_data' INSERT INTO destination_data SELECT * FROM source_data;
Click below to access free SQL quizzes related to Introduction to SQL which will be helpful in your placement exams
SQL INSERT INTO SELECT statement Example
-- Copy new employee data from 'new_hires' to 'employees' INSERT INTO employees (employee_id, first_name, last_name, hire_date) SELECT employee_id, first_name, last_name, hire_date FROM new_hires;
Explanation : We are specifying the columns we want to copy (employee_id, first_name, last_name, and hire_date) from the new_hires table to the corresponding columns in the employees table. This action effectively transfers the data of new employees into the existing employee database.
-- Copy recent orders from 'orders' to 'recent_orders' INSERT INTO recent_orders (order_id, order_date, customer_id, total_amount) SELECT order_id, order_date, customer_id, total_amount FROM orders WHERE order_date >= DATEADD(MONTH, -1, GETDATE());
Explanation :We are using a WHERE clause to filter the records from the orders table where the order_date is within the last month. Only those records meeting the condition will be inserted into the recent_orders table.
-- Copy sales data from 'sales_team_A' and 'sales_team_B' to 'combined_sales' INSERT INTO combined_sales (salesperson_id, sales_date, total_sales) SELECT salesperson_id, sales_date, total_sales FROM sales_team_A UNION ALL SELECT salesperson_id, sales_date, total_sales FROM sales_team_B;
SQL INSERT INTO SELECT multiple rows
- Suppose you have a table called products and another table called order_details. You want to copy multiple rows of product information from the products table into the order_details table for a new batch of orders.
-- Insert multiple rows from 'products' into 'order_details'
INSERT INTO order_details (order_id, product_id, quantity)
SELECT
101, product_id, 5 -- Order 101: Insert 5 units of product_id 1
FROM products
WHERE product_name = 'Product A'
UNION ALL
SELECT
102, product_id, 3 -- Order 102: Insert 3 units of product_id 2
FROM products
WHERE product_name = 'Product B'
UNION ALL
SELECT
103, product_id, 2 -- Order 103: Insert 2 units of product_id 3
FROM products
WHERE product_name = 'Product C';
SQL INSERT INTO SELECT SQL Server
Suppose you have a sales table and an archive_sales table, and you want to copy all sales records from the sales table to the archive_sales table for records older than a year.
-- Copy sales records older than a year to 'archive_sales' INSERT INTO archive_sales (sale_id, sale_date, product_id, amount) SELECT sale_id, sale_date, product_id, amount FROM sales WHERE sale_date < DATEADD(YEAR, -1, GETDATE());
Best Practices SQL INSERT INTO SELECT
Conclusion
In conclusion of Introduction of INSERT INTO SELECT is a powerful SQL command that facilitates data transfer and manipulation within relational databases. Understanding its syntax, use cases, and best practices is essential for efficient data management. You can harness the full potential of this command in your database operations.
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Question 1.
Can I use INSERT INTO SELECT to insert data from multiple source tables into one target table?
Yes, you can. Simply specify multiple source tables in your SELECT statement, and the data will be inserted into the target table accordingly.
Question 2.
What happens if there is a data type mismatch between the source and target columns?
Data type mismatches can lead to errors. Ensure that the data types of the selected columns match the data types of the corresponding columns in the target table.
Question 3.
Is it possible to use INSERT INTO SELECT without specifying columns explicitly?
Yes, you can use INSERT INTO target_table SELECT * FROM source_table; to insert all columns from the source table into the target table.
Question 4.
Can I use INSERT INTO SELECT to insert data from a remote database?
Yes, as long as you have the necessary permissions and connectivity, you can use INSERT INTO SELECT to transfer data between databases.
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
