sql to get data from flat file(zt from asktom)

最新推荐文章于 2024-10-14 22:42:36 发布

congticuan8801

最新推荐文章于 2024-10-14 22:42:36 发布

阅读量162

点赞数

文章标签：数据库

Nag -- Thanks for the question regarding "sql to get data from flat file-- this is mind blowing", version 8.1.7
originally submitted on 12-Oct-2001 19:26 Eastern US time, last updated 26-Apr-2005 20:08

You Asked

"In 9i, with the addition of external tables ( the ability to query a FLAT FILE  
with SQL) -- SQLLDR might be "moot".  Using the external table and the insert 
/*+ append */  I can basically do a direct path load from file to database 
without running a command outside of the database."

Tom

We just cant wait, to see how the above is done, what are the new sql clauses we 
need to use to achieve the above( there should be some new clauses to do the 
above).

Kindly demonstrate the above, this feature will be trend setting and path 
breaking.

Nag

and we said...

Ok, here is a demo I use for training internally:

For external tables, we need to use a directory object -- we'll
start with that, mapping to the temp directory

create or replace directory data_dir as 'c:temp'
/

Now, we'll create the external table.
part of its definition is what looks like a control file -- it is


create table external_table
(EMPNO NUMBER(4) ,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7, 2),
 COMM NUMBER(7, 2),
 DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
  default directory data_dir
  access parameters
  ( fields terminated by ',' )
  location ('emp.dat')
)
/


In tempemp.dat I have a file that looks like this:

7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
....


(its a dump of the emp table in csv format)


Now you can just:

select * from external_table
/



and now, if you modified the scott/tiger EMP table:

delete from emp where mod(empno,2) = 1
/
update emp set sal = sal/2
/
commit;

You could sync up the flat file with the database table using this single 
command:


merge into EMP e1
using EXTERNAL_TABLE e2
on ( e2.empno = e1.empno )
when matched then
    update set e1.sal = e2.sal
when not matched then
    insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
    values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, 
e2.deptno )
/
commit;


that'll update the records in the EMP table from the flat file if they exist OR 
it will insert them.

Doing a direct path load would simply be:

insert /*+ append */ into emp select * from external_table;