CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopersql-insert
Varun Rao
Varun Rao

Reputation: 801

How to generate single insert statement for full table data using SQL Developer?

I have a table with 1,000,000 records in my Oracle Table.

I can use SQL Developer to generate 1,000,000 insert statements as a script.

However to run this script, it takes a lot of time.

Is there any way i can generate 1 single insert statement for all values in the table which when run i know will not take so much time.

Upvotes: 2

Views: 1464

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Is there any way i can generate 1 single insert statement for all values in the table which when run i know will not take so much time.

You could use INSERT ALL statement.

The INSERT ALL statement is used to add multiple rows with a single INSERT statement. The rows can be inserted into one table or multiple tables using only one SQL command.

For example,

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

which is equivalent to 3 INSERT statements.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36832

The simplest method would be to create an anonymous PL/SQL block:

begin
    --add 1 million inserts here
    commit;
end;
/

This eliminates almost all of the network round-trips. It's not the most efficient solution but it's a simple change and is very close to the most efficient solution.

Creating an anonymous block that large may result in an error like PLS-00123: program too large (Diana nodes). If that happens break the program up into smaller chunks and try again.

Upvotes: 1

Related Questions