Batch Data insertion in oracle and oracle

Batch Data insertion in oracle and oracle

Once a function was developed, tens of thousands of pieces of data provided by others need to be imported,

There are about 80 thousand pieces of data in a *. SQL file.

Insert into testtable (id, name) values (1, '1') ----- more than 80 thousand

As usual, all the insert into statements are directly copied to a new SQL window in pl/SQL, and the results are stuck.

Then Open pl/SQL ---> File ---> Open ---> SQL Script and directly execute *. SQL File for import. The result remains stuck.

With the help of colleagues, I used another method to solve this problem:

Open pl/SQL ---> File ---> New ----> Command Window ---> input @, press Enter, and select the File where the data to be imported is located (generally *. SQL file) --- execution successful

Batch insert data into oracle tables

Two methods
1. Use pl/SQL developer
Blog.csdn.net/..72.1690
This method is relatively simple. I have imported tens of thousands of records at this time.

2. sqlldr
First, save your excel file as csv format (default comma interval, save as csv, not just change suffix name)
Use sqlldr to import data. There are many sqlldr parameters, just a few simple ones:
Create a control file control.txt:

Load data
Infile 'f: \ test.csv'
Into table test1
(Id char terminated ',',
Name char terminated ',',
Height char terminated ',',
Birth char terminated by whitespace)

---- Modify the name and path of the data file as needed. The field names and number of fields in the brackets are also modified according to the actual situation. Because csv files are saved and fields are separated by commas, the last field defined in the control file is separated by whitespace. For other fields ','

Then use SQL * loader:
Sqlldr userid = USERNAME/PASSWORD @ XXXX control = f: \ control.txt log = f: \ test. log bad = f: \ testbad.csv

Userid is followed by the username and password, and @ is followed by the network service name, which must be defined in the tnsnames. ora file of the client. The value after control is the path of the created control file. The log is generated at the location specified by the log parameter. If any record is not imported successfully, these records are stored in the file specified after bad.

How to import data to oracle in batches

What database was originally used as ORACLE?
Yes. You can use the PL/SQL tool to import the data, or run "IMP" in CMD"