Meritshot Tutorials

  1. Home
  2. »
  3. SQL INSERT INTO SELECT
SQL

SQL Tutorial

SQL INSERT INTO SELECT

The INSERT INTO SELECT statement is used to insert data into an existing table from the results of a SELECT query. This operation is useful when you need to copy data from one table to another or when you want to insert data based on certain conditions or transformations.

Purpose

  • Copy Data: Allows copying data from one table to another, either within the same database or across different databases.
  • Transform Data: Useful for inserting data based on transformations or aggregations performed in a SELECT query.
  • Data Merging: Facilitates merging data from multiple sources into a single table.

Syntax

INSERT INTO target_table (column1, column2, …)

SELECT column1, column2, …

FROM source_table

WHERE condition;

Example:

1. Insert Data from One Table to Another

Insert employees with salaries greater than 50,000 from the employees table into the high_salary_employees table.

INSERT INTO high_salary_employees (employee_id, first_name, salary)

SELECT employee_id, first_name, salary

FROM employees

WHERE salary > 50000;

Output:
The high_salary_employees table now contains the selected rows.

2. Copy Data with Transformation

Copy data from the orders table to the archived_orders table, where the order date is more than one year old, and calculate a discounted amount.

INSERT INTO archived_orders (order_id, order_date, order_amount, discounted_amount)

SELECT order_id, order_date, order_amount, order_amount * 0.9 AS discounted_amount

FROM orders

WHERE order_date < DATEADD(year, -1, GETDATE());

Output:
The archived_orders table now contains the orders from over a year ago, with discounted amounts.

3. Insert Data from Multiple Tables

Insert aggregated sales data into a monthly_sales_summary table from sales and products tables.

INSERT INTO monthly_sales_summary (month, total_sales)

SELECT MONTH(sale_date) AS month, SUM(sale_amount) AS total_sales

FROM sales

GROUP BY MONTH(sale_date);

Output:
The monthly_sales_summary table now contains the total sales for each month.

4. Combine Data from Multiple Sources

Combine customer data from customers and orders tables into a customer_orders_summary table.

INSERT INTO customer_orders_summary (customer_id, customer_name, order_count)

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id, c.customer_name;

Output:
The customer_orders_summary table now contains the number of orders for each customer.

Tips to Remember

  1. Column Matching: Ensure that the columns in the INSERT INTO clause match the columns in the SELECT query in terms of order and data type.
  2. Existing Table: The target table must already exist with the appropriate structure before using INSERT INTO SELECT.
  3. Data Integrity: Be cautious about data integrity, constraints, and indexes in the target table. Ensure that the data being inserted does not violate constraints.
  4. Performance Considerations: For large data sets, consider performance implications. Indexing and batching can help manage large inserts.

Power of INSERT INTO SELECT

  • Data Migration: Ideal for migrating or copying data between tables or databases.
  • Data Aggregation: Useful for aggregating and summarizing data for reporting or analysis.
  • Batch Processing: Allows for batch processing and transformation of data.

Frequently Asked Questions

Q1: Can I use INSERT INTO SELECT to insert data into a table with different column names?
A1: Yes, you can use INSERT INTO SELECT if the columns in the SELECT query match the target table’s columns by order and type. Use column aliasing if necessary.

Example with Alias:

INSERT INTO target_table (col1, col2)

SELECT column1 AS col1, column2 AS col2

FROM source_table;

Q2: How do I handle duplicate records during the insert operation?
A2: Use constraints such as unique constraints or deduplicate the data in the SELECT query using DISTINCT.

Example:

INSERT INTO target_table (column1, column2)

SELECT DISTINCT column1, column2

FROM source_table;

Q3: Can INSERT INTO SELECT be used with complex joins and subqueries?
A3: Yes, you can use complex joins, aggregations, and subqueries in the SELECT query to insert data into the target table.

Example with Join:

INSERT INTO target_table (customer_id, total_spent)

SELECT c.customer_id, SUM(o.order_amount)

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id;

Q4: What happens if the SELECT query returns no rows?
A4: If the SELECT query returns no rows, no data will be inserted into the target table, and the table will remain unchanged.

Q5: Can I insert data into a table with default values using INSERT INTO SELECT?
A5: Yes, you can insert data into a table with default values. Columns with default values will use those defaults if not specified in the SELECT query.

Example:

INSERT INTO target_table (column1, column2)

SELECT column1, column2

FROM source_table;

If target_table has default values for column3, those defaults will be applied to new rows.

❮ Previous
Next ❯
ads website