Label:1. Create a new file Test.ctl with the following contentLoadDatainfile'Vodall.csv'Append into TableZjsm.vod_record_allfields terminated by '\ t'trailing Nullcols (virtual_column FILLER, Watch_time "To_date (SUBSTRB (: Watch_time,1, +),'YYYY-MM-DD HH24:MI:SS') ", device_id, program_id, Program_name, Program_type, watch_duration number,//default is character type Program_providern AME)Description
InFile ' Vodall.csv ' indicates the file to be read
Append into table Zjsm.vod_reco
Tags: File SQL 3.1 Database Control Errors window rail spanBecause some columns to be imported into the Tmp_content table (column: The TXT or CSV file to import the source file) is empty, also, I was supposedly saved as (in Windows processing) CSV, delimited. This is the column with no content, it should show, and so on.After the control file is modified as follows:-bash-3.1$ more Import_tmp_content.ctlLoad dataInFile '/oracle/wu_li.txt 'Append into t
1. The control file contains the data to be loaded.
First, create a test table.
SQL> showUser
USERIs"ING"
SQL>CreateTableDept
2 (deptno number (10)ConstraintDept_pkPrimaryKey,
3 dname varchar2 (20 ),
4 loc varchar2 (20 ));
The table has been created.
Create a control file (including the loaded data)
[Oracle @ linux sqlldr] $ pwd
/U01/sqlldr
[
SQL * Loader (SQLLDR) is an Oracle high-speed Batch Data Loading tool. It is a very useful tool for loading data to Oralce databases in multiple flat file formats.
SQL * Loader (SQLLDR) is an Oracle high-speed Batch Data Loading tool. It is a very useful tool for loading data to Oralce databases in multiple flat file f
only use the AND expression AND the expression for Equality comparison. In the WHEN clause, the interval (greater than or less than) cannot be used ), no or is null.
4: ability to MERGE (MERGE) data. You can take an operating system file that fills up data and update existing database records.
5: efficient code search. An External table can be joined to another database table as part of the loading process.
6: Using INSERT makes it easier to execute multi-table INSERT. From Oracle9i, you can us
: Release 10.2.0.4.0-Productionon Friday August 3 10:41:44 2012
Copyright (c) 1982,200 7, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0-64bit Production
With the Partitioning, Real applicationters ters, OLAP, Data Mining
And Real Application Testing options
SQL>Configure tnsping
Return to the root user and obtain the necessary files on other
Sql*loader (SQLLDR) is a high-speed batch data loading tool for Oracle. This is a useful tool for loading data into the Oralce database in a variety of flat file formats. SQLLDR can load large amounts of data in a very short period of time. It has two modes of operation.
Traditional Path: (Conventional path): Sqlldr u
creates an external table and loads it using a bulk SQL statement.Generate_only: So that SQLLDR does not load any data specifically, it simply generates the SQL DDL and DML statements that are executed and puts them in the log file that it creates.Note: direct=true covers extenal_table=generate_only. If Direct=true is specified, the data is loaded and no external tables are generated.$ cat Demo1.log--view Sqlldr
SQL * Loader (SQLLDR) is an Oracle high-speed Batch Data Loading tool. This is a very useful tool for loading data to the Oralce database in multiple flat file formats. SQLLDR can load a large amount of data in a very short time. It has two operation modes.Traditional path(Conventional path): SQLLDR uses SQL insert to
editionrelease 10.2.0.4.0-64bit ProductionWith the partitioning, Real applicationclusters, OLAP, Data Miningand Real Application Testing optionsSql>Configure TnspingGo back to the root user and get the necessary files on other Oracle machines.# cd/opt/oracle/instantclient_11_2/# SCP [Email protected]:/oracle/orahome/product/11.2.0/bin/tnsping./The above command
by the business logic.
Iii. Implementation
Step 1 involves specific business logic, which is omitted here.
Step 2: Use the BCP command of ms SQL Server to export data from ms SQL Server and use batch processing to export multiple tables (only one table is listed here). For details, see:
Step 2 use the sqlldr command of Oracle to import the data exported in the previous step to
Oracle
Learn the use of Oracle SQL Loader
One: Features of SQL loader
Oracle has a number of tools that can be used to migrate, backup, and restore data. But each tool has its own characteristics.
For example, exp and IMP can export and export data in the database, which is a good tool for database backup and recovery, so it is mainly used in the hot backup and
export and the use of SQL Loader (SQLLDR)In an Oracle database, we typically use the following methods when replicating or migrating between tables in different databases:1. The record of a table is exported as a semicolon-delimited INSERT statement and then executed into table B2. Create a dblink between the databases and then use CREATE TABLE B as SELECT * from [email protected] where ..., or INSERT INTO
The business data in the group was previously based on the ETL process in the region acquisition-group cleaning-distribution area, and since the group established the software company, the business data required by the subsidiaries must be applied to the group, but the business system's underlying DC did not provide the corresponding data interface. So there's the need to prototype: Get the TXT data file from the remote FTP server regularly and import the data into the local
We all know that lob data processing is very annoying. After the database design is complete, we often need to load test data, which is also annoying for lob. We can use SQL * loader to implement it, following is the control file:Load dataInfile
Label:In practice, the tables in SQL Server need to be imported into Oracle. It is no problem to have previously tried DTS directly with SQL Server. But this time because of the amount of data in tens, so reported insufficient virtual memory. Finally, the solution is to export the TXT file and the Oracle SQLLDR import through SQL Server. One. SQL Server export TX
I. Features of SQL Loader
Oracle has many tools for data migration, backup, and recovery. However, each tool has its own characteristics.
For example, exp and IMP can be used to export and export data in the database, which is a good tool for database backup and recovery. Therefore, exp and IMP are mainly used for hot backup and recovery of the database. It has the advantages of high speed, easy to use, and fast. At the same time, it also has some dis
concrete realizationThe 1th step, which involves specific business logic, is omitted here.2nd, use the MS SQL Server BCP command to export data from MS SQL Server, and use batch processing to export multiple tables together (only one table is listed here), see:3rd step using Oracle's SQLLDR command to import the data from the previous step to Oracle, see: Control files:The 4th step involves the transforma
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.