Insert Into Table From Select Oracle

The INSERT INTO ... Practically speaking, instead of writing procedural loops or external scripts to move data row by row, this single SQL command handles bulk data movement natively within the engine. SELECT statement in Oracle Database is a fundamental yet powerful feature that allows developers and database administrators to populate tables efficiently by leveraging the results of a query. Understanding its syntax, performance implications, and advanced variations is essential for anyone working with data warehousing, ETL processes, or routine database maintenance in an Oracle environment Simple, but easy to overlook. No workaround needed..

Understanding the Core Syntax

At its heart, the command combines the Data Manipulation Language (DML) INSERT statement with a SELECT query. The basic structure requires a target table, an optional column list, and a source query.

INSERT INTO target_table (column1, column2, column3, ...)
SELECT source_col1, source_col2, source_col3, ...
FROM source_table
WHERE condition;

Several rules govern this operation. The number of columns in the INSERT clause must match the number of expressions in the SELECT list. Beyond that, the data types of the selected expressions must be compatible with the target columns—Oracle performs implicit conversion where possible, but explicit casting using CAST or TO_CHAR/TO_DATE is often safer to avoid runtime errors. If the target table column list is omitted, the SELECT list must match the table's defined column order exactly, a practice generally discouraged in production code due to maintenance fragility.

Performance Optimization with Direct-Path Insert

When moving large volumes of data—typically hundreds of thousands or millions of rows—standard INSERT operations generate significant redo and undo logs, consume buffer cache, and can be slow. And oracle offers the /*+ APPEND */ hint (or APPEND_VALUES for INSERT ... VALUES) to enable direct-path insert.

INSERT /*+ APPEND */ INTO sales_history
SELECT * FROM sales_staging
WHERE sale_date < TRUNC(SYSDATE) - 30;

Direct-path insert writes data directly into data files, bypassing the buffer cache and formatting blocks above the High Water Mark (HWM). This drastically reduces redo generation (especially in NOARCHIVELOG mode or when the database is in FORCE LOGGING mode with the table set to NOLOGGING) and speeds up the load considerably Most people skip this — try not to. Surprisingly effective..

Critical Considerations for Direct-Path Insert:

• Locking: It acquires an exclusive lock (TM lock) on the target table, preventing concurrent DML. This makes it unsuitable for highly concurrent OLTP tables during business hours.
• Constraints: Unique and Primary Key constraints are validated at commit time. If violations exist, the entire transaction rolls back.
• Triggers: BEFORE INSERT and AFTER INSERT row-level triggers do not fire for direct-path loads. Statement-level triggers do fire.
• Space: The table's High Water Mark moves up; space is not reused from deleted rows below the HWM. A subsequent ALTER TABLE ... MOVE or TRUNCATE might be needed to reclaim space if the load is temporary.

Handling Errors Gracefully: DML Error Logging

In real-world ETL scenarios, source data often contains "dirty" records—constraint violations, data type mismatches, or nulls in NOT NULL columns. Still, sELECTfails entirely on the first error, rolling back all successful rows processed up to that point. A standardINSERT ... Oracle 10g Release 2 introduced DML Error Logging to solve this.

First, create an error logging table using the DBMS_ERRLOG package:

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'TARGET_TABLE', err_log_table_name => 'TARGET_TABLE_ERR