Forum Stats

  • 3,824,848 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

Add support for multi row INSERT

Lukas Eder
Lukas Eder Member Posts: 126 Bronze Badge
edited May 7, 2020 7:18PM in Database Ideas - Ideas

For quick multi row INSERTs (bulk inserts), it would be very useful to be able to supply multiple rows to the VALUES() constructor (see also )

INSERT INTO my_table (col1, col2)

VALUES (1, 'a'),

       (2, 'b');

The above would do exactly the same thing as the much more verbose

INSERT INTO my_table (col1, col2)

SELECT 1, 'a' FROM dual

UNION ALL

SELECT 2, 'b' FROM dual

Another workaround might be to resort to using PL/SQL's FORALL.

Many other databases already support this form of INSERT statement.

Marked as a duplicate of

Lukas EderFranck PachotAndreasBuckenhoferMKJ10930279Gerald Venzl-OracleWilliam RobertsonSven W.GregVVlad Visan-Oracletonibony7sensoftNiels Heckeryasuo.hondaulohmannTony AndrewsMatthiasRogelApexBineJeffrey KempDirk.NachbarThomas Teske-OracleErik van RoonpnoskoPhilipp SalvisbergSentinelMarkPWSimon MoorePeter Hraškorober584812FatMartinRfac586
28
32 votes

Duplicate · Last Updated

«1

Comments

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Nov 17, 2019 9:20AM

    Or we could use multi-table insert with a dummy SELECT:

    insert all

    into my_table values (1, 'a')

    into my_table values (2, 'b')

    select dummy from dual;

    Edit 2019-11 (original comment 2016): Of course, the limitation of this syntax is that you have to repeat the table and (if writing our SQL properly unlike my example above) the column list for every row, because the multi-table INSERT syntax helpfully allows you to insert into more than one table. I agree it would be great to be able to specify the target once and simply add multiple VALUES clauses to specify additional rows. Voted up.

    FatMartinRrober584812
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    Personally, I'd rather define a "paragraph" of the CSVs using the q'[]' syntax and request Oracle to allow us to .

    Then, it would be just a matter of:

    INSERT INTO T (... )

    SELECT ...

    from csv_data( q'[]', '<column definitions>' )

    Stew Ashton
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    We can do a similar thing using the table function.

    example:

    select * from table(sys.odcinumberlist(1,2,7));

    COLUMN_VALUE

    1

    2

    7

    But it is cumbersome to use. Especially since we depend on using defined object types.

    I'm not sure if the suggested syntax would be my personal favourite. But thats just because I'm so used to the current implementation of VALUES.

    How about :

    select *

    from table((1,'a',7)

                    ,(2,'b',23));

    William Robertson
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Personally, I'd rather define a "paragraph" of the CSVs using the q'[]' syntax and request Oracle to allow us to .

    Then, it would be just a matter of:

    INSERT INTO T (... )

    SELECT ...

    from csv_data( q'[]', '<column definitions>' )

    Parse clobs using external tables? Hm.... the new JSON function are almost there, maybe even better.

    select *

    from json_table(

    q'<{data:[{id:2,name:'a',val:3}

                   ,{id:3,name:'b',val:17}

                   ,{id:5,name:'b'}

                   ]}>'

            ,'$.data[*]'

             columns (rn for ordinality

                     ,id number path '$.id'

                     ,name path '$.name'

                     ,value path '$.val'

                  ));

    RN    ID    NAME    VALUE

    1    2    a    3

    2    3    b    17

    3    5    b    -

  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    We can do a similar thing using the table function.

    example:

    select * from table(sys.odcinumberlist(1,2,7));

    COLUMN_VALUE

    1

    2

    7

    But it is cumbersome to use. Especially since we depend on using defined object types.

    I'm not sure if the suggested syntax would be my personal favourite. But thats just because I'm so used to the current implementation of VALUES.

    How about :

    select *

    from table((1,'a',7)

                    ,(2,'b',23));

    Why invent new syntax when the suggestion is part of the SQL standard and implemented in at least SQL Server and PostgreSQL?

    Sentinel
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    Why invent new syntax when the suggestion is part of the SQL standard and implemented in at least SQL Server and PostgreSQL?

    What Sven has written (JSON_TABLE()) is not new syntax.

    It is currently-available-in-12.1.0.2/just-another-way-to-get-the-job-done-until-SQL xx-standard-is-implemented syntax.

    For the earlier post, ( TABLE() ), ...

    MK

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Why invent new syntax when the suggestion is part of the SQL standard and implemented in at least SQL Server and PostgreSQL?

    Why invent new syntax when we already have insert all?

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Aug 9, 2016 7:34AM

    Why invent new syntax when the suggestion is part of the SQL standard and implemented in at least SQL Server and PostgreSQL?

    Lukas Eder wrote:Why invent new syntax when the suggestion is part of the SQL standard and implemented in at least SQL Server and PostgreSQL?

    The SQL standard documents are pretty hard to read. But as far as I understand them, there is no specific implementation for the constructor requested (table value constructor or contextually typed table value constructors).

    From: http://www.wiscorp.com/sql20nn.zip

    7.3 <table value constructor>FunctionSpecify a set of <row value expression>s to be constructed into a table.Format<table value constructor> ::=VALUES <row value expression list><row value expression list> ::=<table row value expression> [ { <comma> <table row value expression> }... ]<contextually typed table value constructor> ::=VALUES <contextually typed row value expression list><contextually typed row value expression list> ::=<contextually typed row value expression>[ { <comma> <contextually typed row value expression> }... ]

    But there is also this:

    Conformance Rules1) Without Feature F641, “Row and table constructors”, in conforming SQL language, the <contextually typedrow value expression list> of a <contextually typed table value constructor> shall contain exactly one<contextually typed row value constructor> RVE. RVE shall be of the form “(<contextually typed row valueconstructor element list>)”....

    It seems F641 is an optional feature.

    If it is implemented, then your proposed syntax would be needed.

    But the standard also has other options which could be choosen. table VALUE constructors are not the only possibility. There are also query expressions possible. And since query expressions have others usages too, they might be the more obvious choice.

    E.g. an insert can also be done using a

    <table function derived table> which essentially is "TABLE (collection)".

  • Stew Ashton
    Stew Ashton Member Posts: 2,898 Bronze Crown

    I downvoted this suggestion for two reasons:

    1. Unlike Lukas and probably many others, I deal only with Oracle SQL so I am not inconvenienced by not being able to type the same thing in three different databases.
    2. This suggestion might lead to more developers writing parse-heavy code. Something like FORALL allows for reuse of the same statement with multiple binds. This syntax tempts us to litter our code with literals.

    Regards,

    Stew Ashton

  • MarkPW
    MarkPW Member Posts: 6

    Oracle should support this IMO. It's a simple, straightforward syntax that is commonly used in other databases.