Add support for multi row INSERT

Lukas Eder

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

William Robertson

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.

Mike Kutz

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>' )

Sven W.

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));

Sven W.

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

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?

Mike Kutz

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

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.

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

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

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