您现在的位置是：网站首页> 编程资料编程资料

SqlLoader怎么使用_MsSql_

2023-05-26 388人已围观

简介 SqlLoader怎么使用_MsSql_

SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库中加载数据。今天看了申请了*loader的使用，自己小试了下，记录在这

1、假设要插入数据的表ftest，字段是（id,username,password,sj）

2、导入表的数据以txt格式存储，名为data.txt

 1 f f 2010-8-19 2 f1 f1 2010-8-19 3 f2 f2 2010-8-19 4 f3 f3 2010-8-19 5 f4 f4 2010-8-19

3、写控制文件，格式为ctl，命名为cont.ctl 内容如下：

 load data　　　　　　　　　　 infile 'c:\data.txt'　　　　　　 insert into table ftest　　　　 fields terminated by " " (id,username,password,sj)

注：如果表中没有数据就用insert，有数据就用append，删除旧数据插入新的数据用replace或truncate

4 在cmd命令窗口中执行

 sqlldr fyzh/fyzh control=c:\cont.ctl data=c:\data.txt

5 在plsql中查看表ftest

查看已成功插入。

重新学习sqlldr

sqlldr导入数据的一个最简单例子：

 load data infile * --告诉sqlldr要加载的数据就包含在控制文件本身 into table dept --加载到哪个表 fields terminated by ',' --数据加载形式应该是逗号分隔的值 (deptno,dname,loc) --所要加载的列 begindata --告诉sqlldr后面的行市要加载到dept表的数据 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia create table dept (deptno number(2) constraint dept_pk primary key, dname varchar2(14), loc varchar2(13) ) sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctl select * from dept; 1 10 Sales Virginia 2 20 Accounting Virginia 3 30 Consulting Virginia 4 40 Finance Virginia

sqlldr导入的四种加载方式：

APPEND ：原先的表有数据就加在后面
INSERT：装载空表如果原先的表有数据 sqlloader会停止默认值
REPLACE ：原先的表有数据原先的数据会全部删除
TRUNCATE ：指定的内容和replace的相同会用truncate语句删除现存数据

用SQLLDR加载数据的FAQ

1、如何加载定界数据

1)定界数据即用某个特殊字符分隔的数据，可能用引号括起，这是当前平面文件最常见的数据格式。
对于定界数据，最常用的格式是逗号分隔值格式。采用这种文件格式，数据中的每个字段与下一个字段用一个逗号分隔。文本串可以用引号括起，这样就串本身包含逗号。如果串还必须包含引号，一般约定是使用两个引号。加载定界数据，相应的典型控制文件与前面例子相似，但是fields terminated by子句通常如下指定：

 fields terminated by ',' optionally enclose by '"'

它指定用逗号分隔数据字段，每个字段可以用双引号括起。如果把这个控制文件的最后部分修改如下:

 fields terminated by ',' optionally enclosed by '"' (deptno,dname,loc) begindata 10,Sales,"Virginia,USA" 20,Accounting,"Va,""USA""" 30,Consulting,Virginia 40,Finance,Virginia select * from dept 1 10 Sales Virginia,USA 2 20 Accounting Va,"USA" 3 30 Consulting Virginia 4 40 Finance Virginia

2)另一种常用的格式是制表符定界数据。有两种方法使用terminated by子句来加载这种数据：

terminated by X'09' --使用十六进制格式的制表符；若用ASCII，制表符应该是9

 terminated by whitespace --使用terminated by whitespace load data infile * into table dept replace fields terminated by whitespace (deptno,dname,loc) begindata 10 Sales Virginia select * from dept; 1 10 Sales Virginia --使用terminated by X'09' load data infile * into table dept replace fields terminated by X'09' (deptno,dname,loc) begindata 10 Sales Virginia select * from dept; 1 10

Sales --因为一旦遇到一个制表符就会输出一个值。

因此，将10赋给deptno，dname得到了null，因为在第一个制表符和第二个制表符之间没有数据

3）sqlldr的filler关键字使用

如跳过制表符

 load data infile * into table dept replace fields terminated by X'09' (deptno,dummy1 filler,dname,dummy2 filler,loc) begindata 10 Sales Virginia select * from dept; 1 10 Sales Virginia

2、如何加载固定格式数据

要加载定宽的固定位置数据，将会在控制文件中使用position关键字。

 load data infile * into table dept replace (deptno position(1:2), dname position(3:16), loc position(17:29) ) begindata 10Accounting Virginia,USA select * from dept; 1 10 Accounting Virginia,USA

这个控制文件没有使用terminated by子句；而是使用了position来告诉sqlldr 字段从哪里开始，到哪里结束。
对于position，我们可以使用重叠的位置，可以在记录中来回反复。如下修改dept表：

 alter table dept add entire_line varchar(29);

并使用如下控制文件：

 load data infile * into table dept replace (deptno position(1:2), dname position(3:16), loc position(17:29), entire_line position(1:29) ) begindata 10Accounting Virginia,USA select * from dept; 1 10 Accounting Virginia,USA 10Accounting Virginia,USA

使用position时，可以使用相对偏移量，也可以使用绝对偏移量。前面的例子使用了绝对偏移量，明确指定字段从哪开始，从哪结束，也可以将
前面的控制文件改写如下：

 load data infile * into table dept replace (deptno position(1:2), dname position(*:16), loc position(*:29), entire_line position(1:29) ) begindata 10Accounting Virginia,USA

*指示控制文件得出上一个字段在哪里结束。因此，在这种情况下，(*:16)与(3:16)是一样的。注意，控制文件可以混合使用相对位置和绝对位置。
另外，使用*表示法时，可以把它与偏移量相加。例如dname从deptno结束之后的；两个字符开始，可以使用（*+2:16），即相当于（5:16）.

position子句中的结束位置必须是数据结束的绝对列位置。有时，可能指定每个字段的长度更为容易，特别是如果这些字段是连续的。采用这种

方式，只需告诉sqlldr：记录从第一个字节开始，然后指定每个字段的长度。如下：

 load data infile * into table dept replace (deptno position(1) char(2), dname position(*) char(14), loc position(*) char(13), entire_line position(1) char(29) ) begindata 10Accounting Virginia,USA select * from dept;

3、如何加载日期

使用sqlldr加载日期只需在控制文件中date数据类型，并指定要使用的日期掩码。这个日期掩码与数据库中to_char和to_date中使用的日期掩码一样。

如修改dept表如下：

 alter table dept add last_updated date; load data infile * into table dept replace fields terminated by ',' (deptno, dname, loc, last_updated date 'dd/mm/yyyy' ) begindata 10,Accounting,Virginia,1/5/2000 select * from dept; 1 10 Accounting Virginia 2000-5-1

4、如何使用函数加载数据

如果想确保加载的数据是大写的，可以改写控制文件如下：

 load data infile * into table dept replace fields terminated by ',' (deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy' ) begindata 10,Accounting,Virginia,1/5/2000 select * from dept; 1 10 ACCOUNTING VIRGINIA 2000-5-1

如下控制文件加载数据无法导入

 load data infile * into table dept replace fields terminated by ',' (deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy', entire_line ":deptno||:dname||:loc||:last_updated" ) begindata 10,Accounting,Virginia,1/5/2000

1）TRAILING NULLCOLS的使用：一般默认用的好

解决方法，就是使用TRAILING NULLCOLS。这样，如果输入记录中不存在某一列的数据，sqlldr就会为该列绑定一个null值。

这种情况下，增加TRAILING NULLCOLS会导致绑定变量:entire_line成为null。

 load data infile * into table dept replace fields terminated by ',' TRAILING NULLCOLS (deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy', entire_line ":deptno||:dname||:loc||:last_updated" ) begindata 10,Accounting,Virginia,1/5/2000 select * from dept; 1 10 ACCOUNTING VIRGINIA 10AccountingVirginia1/5/2000 2000-5-1

2）case在sqlldr中的使用

假设输入文件中有以下格式的日期：
HH24:MI:SS:只有一个时间；日期时间默认为sysdate
DD/MM/YYYY:只有一个日期，时间默认为午夜0点
HH24:MI:SS DD/MM/YYYY:日期时间都显式提供

可用如下的控制文件

 load data infile * into table dept replace fields terminated by ',' TRAILING NULLCOLS (deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated "case when length(:last_updated)>9 then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'dd/mm/yyyy') end" ) begindata 10,Sales,Virginia,12:03:03 17/10/2005 20,Accounting,Virginia,02:23:54 30,Consulting,Virginia,01:24:00 21/10/2006 40,Finance,Virginia,17/8/2005 alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; select * from dept;

5、如何加载有内嵌换行符的数据

1）用非换行符的其它字符来表示换行符，并在加载时使用一个sql函数用一个CHR(10)替换该文本。

 alter table dept add comments varchar2(4000); --使用下列来加载文本 load data infile * into table dept replace fields terminated by ',' trailing nullcols (deptno, dname "upper(:dname)", loc "upper(:loc)", comments "replace(:comments,'\\n',chr(10))" --'\\n'换行符用chr(10)这个代替 ) begindata 10,Sales,Virginia,this is the sales\noffice in Virginia

注：调用中必须用\\n来表示替换符，而不是\n

2）在infile指令上使用FIX属性，加载一个定长平面文件。
使用该方法，输入数据必须出现在定长记录中。对于固定位置的数据，使用FIX属性就特别合适，这些文件一般为定长文件。
另外使用该方法时，数据必须在外部存储，不能存储在控制文件本身。

 --控制文件 load data infile demo.dat "fix 80" --指定了输入数据文件demo.dat，这个文件中每个记录80字节 into table dept replace fields terminated by ',' trailing nullcols (deptno, dname "upper(:dname)", loc "upper(:loc)", comments ) --数据文件 10,Sales,Virginia,this is the sales\noffice in Virginia 20,，,Sales,Virginia,this is the sales\noffice in Virginia

注：

在unix上，行结束标记是\n即CHR(10),而windows nt平台的行结束标记是\r\n即CHR(13)||CHR(10)；
可以在控制文件中使用trim内置sql函数来完成截断尾部的空白符

 select * from dept;

3)在infile指令在、上使用VAR属性，加载一个变宽文件，在该文件使用的格式中，每一行前几个字节指定了这一行的长度

 --控制文件 load data infile demo.dat "var 3" --表明了前三个字节用于记录每一行的字节数 into table dept replace fields terminated by ',' trailing nullcols (deptno, dname "upper(:dname)", loc "upper(:loc)", comments ) --数据文件 05410,Sales,Virginia,this is the sales office in Virginia

注：在unix上换行符只算一个字节，在windows nt上算两个字节

 select * from dept;

4）在infile指令上使用STR属性，加载一个变宽文件，其中用某个字符序列来表示行结束符，而不是用换行符表示
STR属性以十六进制指定，要得到十六进制串，最容易的办法就是使用sql和utl_raw来生成十六进制串。如在unix平台，行结束标记是CHR(10)，我们的特殊字符是一个管道符号（|），则可以写成：

select utl_raw.cast_to_raw('|'||chr(10)) from dual;--可见在unix上为

上一篇：SQL LOADER错误小结_MsSql_

下一篇：SQLServer存储过程中事务的使用方法_MsSql_

您现在的位置是：网站首页> 编程资料编程资料

SqlLoader怎么使用_MsSql_

相关内容

点击排行

本栏推荐

猜你喜欢