CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Davit khaburdzania
Davit khaburdzania

Reputation: 73

multiple row insert in Oracle SQL

I use multiple row insert syntax in oracle SQL like this:

INSERT ALL
  INTO student(ID, FIRST_NAME, LAST_NAME, AGE)  VALUES(4,'test_name','test_lname',17)
  INTO student(ID, FIRST_NAME, LAST_NAME, AGE)  VALUES(5,'test_name2','test_lname2',20)
  INTO student(ID, FIRST_NAME, LAST_NAME, AGE)  VALUES(6,'test_name3','test_lname3',21)
  select * from dual;

can anyone explain me what is the meaning of using

select * from dual

at the and of statement?

Upvotes: 4

Views: 11111

Answers (3)

thatjeffsmith
thatjeffsmith

Reputation: 22467

This is now possible, without any tricks, using Database 23c.

create table if not exists NEW_OLD_DEMO (
   ID       number
      generated by default as identity ( start with 1 cache 20 )
   , NAME   varchar2(50)
   , SALARY number
);

alter table NEW_OLD_DEMO add constraint NEW_OLD_DEMO_PK primary key ( ID )
   using index;
    
insert into NEW_OLD_DEMO (NAME, SALARY)
values ('Smitty', 10),
        ('Kristopher', 20 ),
        ('Larry', 30);

And querying the data back out -

enter image description here

Docs:

Not that you're limited to passing 65535 entries via the VALUES clause. Also, this will be done as a single transaction.

And this wouldn't be a good answer for Oracle w/o a reference to the oracle-base example for using this feature, across insert, merge, with, and select.

Upvotes: 3

shareef
shareef

Reputation: 9581

it the syntax for INSERT ALL

INSERT ALL
INTO <table_name> VALUES <column_name_list)
INTO <table_name> VALUES <column_name_list)
...
<SELECT Statement>;

if there is nothing you want to select after inserting you do select * from dual

otherwise you do your select you want usually to confirm the insert success

reference

Upvotes: 6

Simon Dorociak
Simon Dorociak

Reputation: 33515

The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.

More about this here and here.

Upvotes: 4

Related Questions