Sqlldr Importing data (take postgresql>>>oracle as an example)

1. Create a table in the target database

1.1 Click the source table to copy the creation statement

1.2 Modify the data type to match the target database, such as:

String type: Character varying (20) >>>varchar2

Number type: Numeric (19,2) >>>number (19,2)

Timestamp: Timestamp without time zone>>>timestamp or date

1.3 Other

The general string type needs to be extended length, otherwise sometimes will not plug in data, do not know why;

The NOT NULL default * statement for the P and O databases is exactly the opposite;

2. Export CSV Format data

Copy call to '/tmp/call.csv ' DELIMITER ', ' NULL ' csv [QUOTE ' "' Force QUOTE with all column names of this table]

This allows the data to be exported, and it is important to note that:

① sometimes a large amount of data can occur when the export fails, such as

WORKAROUND: Export with Select

②[quote ' "' Force QUOTE as an array of all column names for this table"

No, the data for each column is added "", in PostgreSQL, the statement that gets the array form of all the column names of the table is as follows:

1  SELECTarray_to_string (Array (2             SelectAttName fromPg_attributewhereAttrelid= 'Table name':: Regclass andAttnum> 03              andattisdropped= 'F'   4),',') asName

Interested to try.

3. Download the CSV file

4. Edit the *.ctl control file

1 LoadData2 CHARACTERSET UTF83InFile "/Home/Oracle/hthhf.csv "4 truncate5  into TableT_yw_hthhf_old6Fields terminated by ","7Optionally enclosed by '"'8 trailing Nullcols9 (TenColumn Name 1integerExternal, OneColumn Name 2timestamp"YYYY-Mm-DD hh24:mi:ss ", A Column Name 3, -Column Name 4floatExternal -)

Indicates that the/HOME/ORACLE/HTHHF.CSV data is passed into the table t_yw_hthhf_old, separated by commas. Note that the following brackets, the string type is not modified, integer and floating-point type are added integer/float external, date plus timestamp "Yyyy-mm-dd hh24:mi:ss".

5. Upload the CSV and CTL files to the target server (slightly);

6. Switch to the Oracle user on the target server and execute the command:

Sqlldr userid= user name/password control=ctl The full path of the file;

For example, I uploaded to/home/oracle, then Control=/home/oracle/hthhf.ctl

7. Other issues

After importing need to look at the target database, check whether to import all, if the data is not complete, or in the execution of the command when the exception, you need to view the CTL file with the same directory log, the same name as the CTL, which will log the cause of the error. The errors you have seen now are: the contents of the field contain line breaks or field lengths, and so on.

Sqlldr Importing data (take postgresql>>>oracle as an example)