Forum Stats

  • 3,874,176 Users
  • 2,266,677 Discussions
  • 7,911,754 Comments

Discussions

Add support for the VALUES() constructor

Lukas Eder
Lukas Eder Member Posts: 126 Bronze Badge
edited Jul 30, 2018 11:33AM in Database Ideas - Ideas

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

Lukas Ederddf_dbaberxFranck Pachottonibony7Niels HeckerGbenga Ajakayeulohmanncormacojnicholas330Thorsten Kettner2887900BPeaslandDBASven W.Sebastien A.Emad Al-MousaManik3471715Gerald Venzl-OracleAlexey Marinuser2234072Mike KutzJoergJost2662209Brian BakulaPeter HraškoUser_S1LHLUser_ORA1AL. FernigriniShankarS-Oraclefac586Stefan ZwanenburgJbartels-OracleLoïc Lefèvre-OracleSentinel4106787dirkvanhaute47f8e2b8-56c3-4756-9265-f45bde39be4ac075c3a9-249c-4358-b5e8-08998e0afc26Göran Pauesf488ccdf-4824-4939-9cd8-5d4641f88db4User_930JVAstentxuser11198823User_E6XWLYevon
47 votes

Under Review - Voting Still Open · Last Updated

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,253 Gold Crown

    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)
    Lukas Eder
  • connor_mc_d-Oracle
    connor_mc_d-Oracle Posts: 88 Employee
    edited Nov 12, 2022 1:33PM

    A VALUES constructor comes in 23c.

    (Standard safe harbour applies, what you see in the 23c beta release may or may not end up production etc)