Add support for the VALUES() constructor

Lukas Eder

Technically, an INSERT statement always transfers data from one table to another, regardless if we're using the INSERT .. VALUES syntax or the INSERT .. SELECT syntax. This is because the VALUES() constructor is in fact a standalone table according to the SQL standard. The following syntax is thus a valid query in SQL (as implemented by SQL Server and PostgreSQL):

VALUES(1), (2)

The above query can currently be emulated (quite tediously) in Oracle using:

SELECT 1 FROM dual

UNION ALL

SELECT 2 FROM dual

Of course, the syntax is not restricted to producing only one column. Here's a two-column version:

VALUES(1, 'a'),

      (2, 'b')

And finally, this feature probably depends on derived column lists being available (), which is the only way to give such a table (and its columns) a name:

SELECT *

FROM (

  VALUES(1, 'a'),

        (2, 'b')

) t(a, b)

Now, this construct is really extremely useful when producing ad-hoc cross joins and other data with hard-coded constant tables.

Related:

This request is now referenced as: ENH 28424374 - ADD SUPPORT FOR THE ISO SQL STANDARD VALUES() CONSTRUCTOR

Mike Kutz

Primary usefulness for the SQL syntax seems to be:

• Generate sample data (eg this Forum)
• Initialize (or add to) a Dimension table with multiple rows.

Additionally, other software should be able to generate such syntax similar to how they can generate INSERT statements.

1. SQL*Developer /*insert*/ hint.
2. SQL*Developer Export Table option
3. SQLcl (SET SQLFORMAT insert)