select from one table, insert into another table oracle sql query

Efficiently Copy Data: SELECT INTO INSERT in Oracle SQL

Learn how to select data from one table and insert it into another table in Oracle SQL, covering various scenarios and best practices for data migration and replication.

Copying data between tables is a fundamental operation in database management. Whether you're migrating data, creating backups, populating staging tables, or replicating information, Oracle SQL provides powerful and flexible ways to achieve this. This article will guide you through the process of selecting data from one table and inserting it into another, covering basic syntax, conditional inserts, and considerations for performance and data integrity.

Basic SELECT INTO INSERT Statement

The most straightforward way to copy data from one table to another is by using an INSERT INTO ... SELECT FROM statement. This command allows you to specify the target table and columns, and then use a SELECT query to retrieve the data from the source table. It's crucial that the data types and order of columns in the SELECT statement match the target table's columns.

INSERT INTO target_table (column1, column2, column3)
SELECT source_column1, source_column2, source_column3
FROM source_table
WHERE condition;

Basic syntax for inserting selected data into another table.

Understanding the Data Flow

The process involves reading rows from the source table based on the SELECT statement's criteria and then writing those rows as new records into the target table. This operation is atomic; either all selected rows are inserted, or none are. This ensures data consistency during the transfer.

flowchart TD
    A[Start] --> B{SELECT data from Source_Table};
    B --> C{Filter data with WHERE clause (optional)};
    C --> D{Prepare data for insertion};
    D --> E[INSERT data into Target_Table];
    E --> F[End];

Data flow for SELECT INTO INSERT operation.

Handling Different Scenarios

The INSERT INTO ... SELECT FROM statement is highly versatile and can be adapted for various scenarios, including conditional inserts, inserting into a new table, and using subqueries.

1. Inserting All Columns

INSERT INTO employees_backup
SELECT * FROM employees
WHERE department_id = 10;

Inserting all columns for employees in department 10 into a backup table.

2. Inserting Specific Columns

INSERT INTO new_hires (employee_id, first_name, last_name, hire_date)
SELECT employee_id, first_name, last_name, SYSDATE
FROM applicants
WHERE status = 'ACCEPTED';

Inserting specific columns from an applicants table into a new hires table, with a calculated hire_date.

3. Conditional Inserts with WHERE Clause

INSERT INTO archived_orders (order_id, customer_id, order_date, total_amount)
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date < ADD_MONTHS(SYSDATE, -12);

Archiving orders older than 12 months.

4. Creating a New Table and Inserting Data (CREATE TABLE AS SELECT)

While not strictly INSERT INTO ... SELECT FROM, the CREATE TABLE AS SELECT (CTAS) statement is often used for similar purposes when the target table does not yet exist. It creates the table structure and populates it with data in a single operation.

CREATE TABLE high_value_customers AS
SELECT customer_id, customer_name, total_spent
FROM customers
WHERE total_spent > 10000;

Creating a new table 'high_value_customers' from existing customer data.

Performance Considerations and Best Practices

When dealing with large datasets, performance is a critical factor. Here are some tips to optimize your INSERT INTO ... SELECT FROM operations:

1. Disable Indexes and Constraints (Temporarily)

For very large inserts, temporarily disabling indexes and constraints on the target table can significantly speed up the operation. Re-enable them after the insert is complete. This reduces the overhead of maintaining these structures during the data load.

2. Use NOLOGGING Option

If you are creating a new table using CREATE TABLE AS SELECT or performing a direct-path insert (which Oracle often does automatically for INSERT /*+ APPEND */), consider using the NOLOGGING option. This minimizes the generation of redo logs, improving performance, but it means you cannot recover the operation using redo logs in case of failure. Ensure you have a backup strategy.

3. Commit Frequency

For INSERT statements, Oracle typically commits the entire transaction at once. If you're inserting millions of rows, this can consume a lot of undo space. Consider breaking large inserts into smaller batches with explicit COMMIT statements if your application logic allows, though this is less common for a single INSERT INTO ... SELECT statement.

4. Parallel Processing

For very large tables, you can hint Oracle to use parallel execution for both the SELECT and INSERT parts of the statement to leverage multiple CPU cores. Example: INSERT /*+ APPEND PARALLEL */ INTO target_table SELECT /*+ PARALLEL */ ...

5. Match Data Types

Ensure that the data types of the selected columns precisely match the data types of the target columns. Mismatches can lead to implicit conversions, which can impact performance or even cause errors.