[达梦数据库]dmfldr快速加载使用示例

1 参数

1.1 DATA

指定数据文件路径。

SQL> create table test(C1 INT,C2 INT,C3 DATE);

[dmdba@DM8 soft]$ cat test.txt 
1 1|2015-11-06
2 2|2015-11-05
3 3|2015-11-04

[dmdba@DM8 soft]$ cat test.ctrl 
LOAD DATA
INFILE *
INTO TABLE test
FIELDS '|'
(
C1 TERMINATED BY ' ',
C2,
C3 DATE FORMAT 'yyyy-mm-dd'
)

[dmdba@DM8 soft]$ dmfldr userid=SYSDBA/SYSDBA control=\'/dm/soft/test.ctrl\' data=\'/dm/soft/test.txt\'

SQL> select * from test;
行号     C1          C2          C3                 
---------- ----------- ----------- -------------------
1          1           1           2015-11-06 00:00:00
2          2           2           2015-11-05 00:00:00
3          3           3           2015-11-04 00:00:00

1.2 BADFILE

错误数据记录文件。默认生成fldr.bad 文件。

SQL> CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);

[dmdba@DM8 soft]$ cat test.txt 
1 1|2015-11-06
2 2|2015-11-05
3 3|2015-11_04
44|aaaa-bbb-ccc

[dmdba@DM8 soft]$ cat test.ctrl 
LOAD DATA
INFILE '/dm/soft/test.txt'
INTO TABLE test
FIELDS '|'
(
C1 TERMINATED BY ' ',
C2,
C3 DATE FORMAT 'yyyy-mm-dd'
)

[dmdba@DM8 soft]$ dmfldr userid=SYSDBA/SYSDBA control=\'/dm/soft/test.ctrl\' badfile=\'/dm/soft/test.bad\'

SQL> select * from test;
行号     C1          C2          C3                 
---------- ----------- ----------- -------------------
1          1           1           2015-11-06 00:00:00
2          2           2           2015-11-05 00:00:00
3          3           3           2015-11-04 00:00:00

[dmdba@DM8 soft]$ cat test.bad 
dmfldr: 2021-02-01 05:28:26 TEST 44|aaaa-bbb-ccc

1.3 LOB_DIRECTORY

大字段数据文件存放目录

1.4 LOB_FILE_NAME

大字段数据文件名称，仅导出有效 (dmfldr.lob)

1.5 SET_IDENTITY

自增列，默认FALSE，TRUE时不受自增列设置影响，插入原数据。

SQL> CREATE TABLE TEST(C1 INT IDENTITY(1,1),C2 VARCHAR);

SQL> INSERT INTO TEST(C2) VALUES('AAA');
SQL> INSERT INTO TEST(C2) VALUES('BBB');
SQL> commit;
SQL> select * from test;
行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB

[dmdba@DM8 soft]$ cat test.txt 
2|aaa
3|bbb
4|ccc

[dmdba@DM8 soft]$ cat test.ctrl 
LOAD DATA
INFILE '/dm/soft/test.txt'
INTO TABLE test
FIELDS '|'
(
C1,
C2
)

[dmdba@DM8 soft]$ dmfldr userid=SYSDBA/SYSDBA control=\'/dm/soft/test.ctrl\' set_identity=false

SQL> select * from test;    
行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           aaa
4          4           bbb
5          5           ccc

[dmdba@DM8 soft]$ dmfldr userid=SYSDBA/SYSDBA control=\'/dm/soft/test.ctrl\' set_identity=true

SQL> select * from test;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          2           aaa
4          3           bbb
5          4           ccc

1.6 when

条件筛选。

SQL> CREATE TABLE TEST(C1 INT,C2 INT);

[dmdba@DM8 soft]$ cat test.txt 
12
23
32
48
91

[dmdba@DM8 soft]$ cat test.ctrl 
LOAD DATA
INFILE '/dm/soft/test.txt'
INTO TABLE test
WHEN C1 != '2'
(
C1 position (1:1),
C2 position (2:2)
)

[dmdba@DM8 soft]$ dmfldr userid=SYSDBA/SYSDBA control=\'/dm/soft/test.ctrl\'

SQL> select * from test;

行号     C1          C2         
---------- ----------- -----------
1          1           2
2          3           2
3          4           8
4          9           1

1.7 INTO TABLE

导入多表。

SQL> CREATE TABLE TEST1(C1 INT,C2 INT);
SQL> CREATE TABLE TEST2(C1 INT,C2 INT);

[dmdba@DM8 soft]$ cat test.txt 
1,2
2,3
3,2
4,8
9,1

[dmdba@DM8 soft]$ dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/dm/soft/test.ctrl\'

SQL> select * from test1;
行号     C1          C2         
---------- ----------- -----------
1          2           NULL
2          3           NULL
3          4           NULL
4          9           NULL

SQL> select * from test2;
行号     C1          C2         
---------- ----------- -----------
1          1           NULL

1.8 SKIP、LOAD

SKIP:跳过初始几列。
LOAD:一共执行的列数。

SQL> CREATE TABLE TEST(C1 INT,C2 VARCHAR);

[dmdba@DM8 soft]$ cat test.txt 
1|aaa
2|bbb
3|ccc
4|ddd
5|eee
6|fff
7|ggg
8|hhh
9|iii
10|jjj

[dmdba@DM8 soft]$ cat test.ctrl 
LOAD DATA
INFILE '/dm/soft/test.txt'
INTO TABLE test
FIELDS '|'
(
C1,
C2
)

[dmdba@DM8 soft]$ dmfldr userid=SYSDBA/SYSDBA control=\'/dm/soft/test.ctrl\' skip=3 load=5

SQL> select * from test;
行号     C1          C2 
---------- ----------- ---
1          4           ddd
2          5           eee
3          6           fff
4          7           ggg
5          8           hhh