Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to import data into oracle". In daily operation, I believe many people have doubts about how to import data into oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to import data into oracle"! Next, please follow the editor to study!

Method one, use SQL*Loader

This is a more widely used method, provided that the target table in the oracle data already exists.

The general steps are as follows:

1 Save the excel file as a new file, for example, the file name is text.txt, and the file type is text file (tab delimited). It is OK to choose the type csv (comma separated) here, but change the field Terminator to','(fields terminated by',') when writing the following control.ctl, assuming saving to the root directory of the c disk.

2 if there is no existing table structure, it is created, assuming that the table is test, with two columns of dm,ms.

3 use notepad to create SQL*Loader control files, online that the file name suffix is ctl, in fact, I found that I can also use the txt suffix. For example, it is named control.ctl, and the content is as follows: (--the following is a comment, which is not actually needed)

Load data-controls file identification

Infile'c:\ text.txt'-- the data file to be imported is named test.txt

Append into table test-append records to table test

Fields terminated by TAB 09'-- the field terminates at Xdialog 09, which is a tab (Tab)

(dm,ms)-defines the corresponding order of columns

Note: the append used in the above example is as follows: insert, which is the default, requires the table to be empty at the beginning of data loading; append, which appends new records to the table; and replace, which deletes the old record and replaces it with the newly loaded record.

; truncate, same as replace.

4 use the SQL*Loader command to input data at the command line prompt

Sqlldr userid=system/manager control='c:\ control.ctl'

Let me give you an example.

1. Export the test.csv file of the region table

"01", "downtown"

"02", "Keqiao"

"03", "Qian Qing"

"04", "Gao Port"

"05", "Lizhu"

"06", "Qi Xian"

"08", "Paojiang"

"09", "Ping Shui"

"12", "Dongpu"

....

two。 Edit the sqlldr export control file test.ctl as follows:

Load data

Infile'e:\ test.csv'

Replace into table test

Fields terminated by', 'optionally enclosed by' "'

Trailing nullcols

(id,name)

3. Execute in cmd:

E:\ > sqlldr userid=xxx/yyy@dbxx control=e:\ test.ctl log=e:\ log.txt bad=e:\ b

Ad.txt

SQL*Loader: Release 8.1.7.0.0-Production on Wednesday July 20 21:28:06 2005

(C) Copyright 2000 Oracle Corporation. All rights reserved.

Submit point reached, logical record count 48

E:\ >

For more details, please see http://www-rohan.sdsu.edu/doc/oracle/server803/A54652_01/ch04.htm#793.

Method 2 using PLSQL Developer

Use the PLSQL Developer tool, the most commonly used tool in the famous Oracle DBA.

In cases where a single file is small (less than 100000 lines), and the destination table structure already exists-certainly not more than for excel, because the maximum behavior of the excel file is 65536Mel-you can select all the data COPY and then use the PLSQL Developer tool.

1 enter select * from test for update in the sql window of PLSQL Developer

2 press F8 to execute

Open the lock and press the plus sign again. Click the mouse to the column head of the first column, make all the columns selected, then paste, and finally submit the COMMIT.

There are also many third-party tools, Toad,PL/SQL Developer,SQL Navigator and so on.

Method 3 other data transfer tools are used as transfer stations.

I am using Sql Server 2000.

Import the excel table into the sqlserver database and then from Sql server to oracle. These two parts are easy to operate, if you don't know how to check for help.

At this point, the study on "how to import data into oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.