Search code examples
databaseoracleoracle-sqldeveloper

Execute multiple insert statements as batch in SQLDeveloper

I have a script which has around 2600 insert statements on a single table. Like below

insert into STUDENT(NAME, ROLL) VALUES('Bob', 1);
insert into STUDENT(NAME, ROLL) VALUES('Ron', 2);
.
.
insert into STUDENT(NAME, ROLL) VALUES('Jack', 2600);

I am running this script (Oracle DB) by pressing F9 and it starts executing line by line and takes lot of time.

Is there a way I can execute them all at once?

NOTE: The insert script is generated through export and I cannot change it in the below format

insert into STUDENT
VALUES
('Bob', 1),
('Ron', 2),
.
.
('Jack', 2600);
Solution

  • Script contains separate INSERT INTO statements and they are executed as such - separately, one-by-one.

    If you exported data into an e.g. CSV file, you could have

    • used SQL*Loader or
    • external tables feature

    to do it much faster. But, as you didn't, let the script run and wait a while. I don't think that you have any other option (unless you separate it into several smaller files and run them in separate SQL*Plus connections in parallel, but that would probably take more time to prepare than wait for current script to finish).

    As of SQL*Loader:

    • first, make sure you have data in a textual file (separated by e.g. semi-colon)
    • check whether you have SQL*Loader on your computer. It is installed along with any Oracle database or - if you don't have it on your PC - Oracle Client software
    • then create a control file which uses the CSV file as input and loads data into appropriate columns in the target table

    This is Oracle 11g SQL*Loader documentation. It takes time to read it but - regarding the fact that it is a simple two-column table, should be fairly simple to do it.