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.
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.
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.
