Oracle sql loader 导数据时添加序号的三种方法

1.用触发器和序列实现

CREATE SEQUENCE u.seq_questionno

START WITH 0

MAXVALUE 999999999999999999999999999

MINVALUE 0

NOCYCLE

NOCACHE

NOORDER;

CREATE OR REPLACE TRIGGER u.tr1

   BEFORE INSERT

ON u.t1

   REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

BEGIN

SELECT c1.NEXTVAL

INTO :NEW.c1

FROM DUAL;

END;

控制文件:

LOAD DATA  INFILE 'G:a.txt'  BADFILE 'G:a.bad' DISCARDFILE 'G:a.dsc' INTO TABLE "T1" APPEND FIELDS TERMINATED BY X'9' TRAILING NULLCOLS (      C2,     C3,      C4,     C5     -- C1 )

2.直接在控制文件中加入序列

LOAD DATA  INFILE 'G:a.txt'  BADFILE 'G:a.bad' DISCARDFILE 'G:a.dsc' INTO TABLE "T1" APPEND FIELDS TERMINATED BY X'9' TRAILING NULLCOLS (      C2,     C3,      C4,     C5,     C1 "SEQ_QUESTIONNO.nextval" )

3.不用序列,在控制文件中使用 RECNUM

LOAD DATA  INFILE 'G:a.txt'  BADFILE 'G:a.bad' DISCARDFILE 'G:a.dsc' INTO TABLE "T1" APPEND FIELDS TERMINATED BY X'9' TRAILING NULLCOLS (     C1 RECNUM,     C2,     C3,      C4,     C5 )