in ORACLE DB SQL, how to do an 'Insert all' statement where i only specify the columns once?

Question

right now i do the following

    INSERT ALL 
    into "table" (col1, col2, col3....) values (val1,val2,val3...)
    ...
    select 1 from dual;

However, that requires the sql code to include the columns for each insert (i care about this because i am storing this sql statement as a .sql file, and it takes like 200mb, would like to it to be smaller). Is there a way i can do the following? assume that all inserts are for the same table:

    INSERT ALL INTO "Table" (col1,col2,col3...)
    Values
    (val1,val2,val3...)
    (val1,val2,val3...)
    ...
    select 1 from dual;

because this would cut the file size in half

Answer 1

If all data goes into the same table, why don't you then store data only and load it into the target table much faster than sloooow insert-by-insert?

Here's an option which uses external table. It requires directory to be created and read/write privileges granted to user who will be using it. DBA usually creates it. As I already have that set:

SQL> select directory_name, directory_path from dba_directories where directory_name = 'EXT_DIR';

DIRECTORY_NAME       DIRECTORY_PATH
-------------------- --------------------
EXT_DIR              c:\temp

I won't be doing it again; ask if you need assistance.

---

Sample data is stored in data_for_test.txt file, located in my c:\temp directory. How come it is on my local PC? Because I'm running Oracle 21cXE on my laptop. File contents:

1, Little, 1000
2, Foot, 1200
3, Scott, 2000
4, Tiger, 1800

Target table:

SQL> create table test
  2    (id      number,
  3     name    varchar2(20),
  4     salary  number
  5    );

Table created.

SQL> select * From test;

no rows selected

Let's create external table; it acts as if it were a "view" into the file:

SQL> create table ext_test
  2    (id     number,
  3     name   varchar2(20),
  4     salary number
  5    )
  6  organization external
  7    (type oracle_loader
  8     default directory ext_dir
  9     access parameters
 10       (records delimited by newline
 11        fields terminated by ','
 12        missing field values are null
 13       )
 14     location ('data_for_test.txt')
 15    )
 16  reject limit unlimited;

Table created.

Select from it:

SQL> select * From ext_test;

        ID NAME                     SALARY
---------- -------------------- ----------
         1  Little                    1000
         2  Foot                      1200
         3  Scott                     2000
         4  Tiger                     1800

Fine, everything is here. Finally, insert data into the target table:

SQL> insert into test (id, name, salary)
  2  select id, name, salary from ext_test;

4 rows created.

SQL> select * from test;

        ID NAME                     SALARY
---------- -------------------- ----------
         1  Little                    1000
         2  Foot                      1200
         3  Scott                     2000
         4  Tiger                     1800
    
SQL> truncate table test;

Table truncated.

SQL>

Done.

---

Another option is to use even faster SQL*Loader utility. Its advantage is that it is - as I said - very fast and it allows source data file to be stored on your own computer, regardless where the database is.

You'll need a control file which instructs the utility what to do (its name is, in my example, test12.ctl):

load data
infile "c:\temp\data_for_test.txt"
replace
into table test
fields terminated by ','
trailing nullcols

(
  id,
  name,
  salary 
)

Loading is invoked from the operating system command prompt:

c:\temp>sqlldr scott/tiger@pdb1 control=test12.ctl log=test12.log

SQL*Loader: Release 21.0.0.0.0 - Production on Fri Apr 7 21:25:58 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4

Table TEST:
  4 Rows successfully loaded.

Check the log file:
  test12.log
for more information about the load.

Result:

c:\temp>sqlplus scott/tiger@pdb1

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 7 21:26:15 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Fri Apr 07 2023 21:26:00 +02:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> select * from test;

        ID NAME                     SALARY
---------- -------------------- ----------
         1  Little                    1000
         2  Foot                      1200
         3  Scott                     2000
         4  Tiger                     1800

SQL>

---

Now you have two options to think about. If I were you, I'd discard your current option.

Answer 2

Use UNION ALL to insert multiple rows in a single SQL statement without having to specify the columns for every row:

create table my_table(col1 number, col2 number, col3 number, col4 number);

insert into my_table(col1, col2, col3)
select 1, 2, 3 from dual union all
select 4, 5, 6 from dual union all
...
select 7, 8, 9 from dual;

But beware that creating one enormous INSERT statement can cause performance problems. Large SQL statements work much better than most people think, but 200MB SQL statements will likely cause problems. You'll want to split your statements until they only return hundreds of rows at a time.

Note that Littlefoot's answer is likely the fastest way to load data. However, there are many cases where we don't want our programs or deployments to depend on the presence of a client tool like SQL*Loader, and there are many cases where we can't load a file onto the database server and use an external table. It's useful to have multiple approaches to loading data.

If you're using version 23c, there is an improved table values constructor syntax that makes this task even easier:

insert into my_table(col1, col2, col3) values
(1,2,3),
(4,5,6),
(7,8,9);