Table Value Constructors in Oracle 26ai: Multi-Row INSERT and Inline Data with the VALUES Clause

/ 26ai New Features / By

The End of UNION ALL FROM DUAL

For decades, Oracle developers have endured one of the most verbose patterns in SQL: inserting multiple rows using SELECT ... FROM DUAL UNION ALL. Oracle Database 26ai finally introduces Table Value Constructors – a SQL-standard feature that lets you specify multiple rows of data inline using the VALUES clause. This brings Oracle in line with PostgreSQL, SQL Server, MySQL, and the ANSI SQL standard.

Table Value Constructors work in INSERT statements, FROM clauses, subqueries, MERGE, and CREATE TABLE AS SELECT – anywhere you need to express an inline set of rows without creating a physical table.

Practical Examples

1. Multi-Row INSERT in a Single Statement

The most common use case: inserting multiple rows without repeating INSERT INTO or chaining UNION ALL queries against DUAL.

-- Old way (Oracle 23ai and earlier)
INSERT INTO employees (id, name, dept) SELECT 1, 'Alice', 'Eng' FROM DUAL UNION ALL
SELECT 2, 'Bob', 'Sales' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Eng' FROM DUAL;

-- New way (Oracle 26ai)
INSERT INTO employees (id, name, dept)
VALUES (1, 'Alice', 'Eng'),
       (2, 'Bob', 'Sales'),
       (3, 'Carol', 'Eng');

One statement, three rows, zero boilerplate. The parser processes a single SQL statement instead of three unioned queries, which can reduce parse overhead for large row sets.

2. Inline Reference Data in the FROM Clause

Need ad-hoc lookup data for a join without creating a temporary table? Use VALUES directly in the FROM clause.

SELECT e.name, e.dept, s.status_name
FROM   employees e
JOIN   (VALUES (1, 'Active'),
               (2, 'Inactive'),
               (3, 'On Leave')) AS s(status_id, status_name)
ON     e.status_id = s.status_id;

This is invaluable for prototyping queries, writing reports with inline mapping tables, and building test harnesses – all without DDL privileges or cluttering the schema with helper tables.

3. Data Seeding with MERGE

Table Value Constructors integrate cleanly with MERGE, making upsert-style data seeding scripts portable and readable.

MERGE INTO config_params t
USING (VALUES ('max_retries', '3'),
              ('timeout_ms', '5000'),
              ('log_level', 'INFO')) AS s(param_name, param_value)
ON (t.param_name = s.param_name)
WHEN MATCHED THEN
  UPDATE SET t.param_value = s.param_value
WHEN NOT MATCHED THEN
  INSERT (param_name, param_value)
  VALUES (s.param_name, s.param_value);

This pattern is perfect for application deployment scripts where configuration data must be idempotently applied across environments.

4. CREATE TABLE AS SELECT with Inline Data

Quickly spin up a table with predefined data – useful for test fixtures, staging tables, and migration scripts.

CREATE TABLE status_lookup AS
SELECT * FROM (VALUES (1, 'Active'),
                      (2, 'Inactive'),
                      (3, 'Pending'),
                      (4, 'Archived')) AS t(status_id, status_name);

No DUAL, no UNION ALL, no PL/SQL – just a clean, declarative statement.

Key Takeaways

  • Cleaner multi-row inserts: Replace verbose UNION ALL FROM DUAL patterns with a standard, comma-separated VALUES list.
  • Inline row sources: Use VALUES in FROM clauses for ad-hoc reference data, joins, and prototyping without creating physical tables.
  • Full DML integration: Works with INSERT, MERGE, CTAS, and subqueries – covering ETL, data seeding, testing, and migration scenarios.
  • Cross-database portability: SQL written with Table Value Constructors is more portable across PostgreSQL, SQL Server, and other SQL-standard databases.
  • Reduced parse overhead: A single SQL statement with multiple value rows is more efficient to parse than multiple individual INSERT statements or deeply nested UNION ALL queries.

Table Value Constructors in Oracle 26ai are a small syntactic change with a big practical impact. If you’ve ever wished Oracle’s SQL felt more modern, this is exactly the kind of quality-of-life improvement that adds up across every script, migration, and test suite you write.

Scroll to Top