数据导入文档.docx

档案数据入库操作文书数据入库删除原有表格内容选中表T_WS_DOCINDEX内所有内容进行删除导入mdb索引也可能是excle文件（1）打开PLSQL，工具—ODBC导入器：（2）ODBC导入器具体设置：“来自ODBC的数据”：??选择“MS Access Database”；??输入数据库登陆信息；??点击connect ，选择需要导入的mdb文件；??选择具体的表。如下图所示填写选择并导入正确的索引表“到Oracle的数据”：??输入用户与导入的数据库表名；??指定字段对应关系以及字段类型；??点击import导入。如下图所示填写选择。字段填充填充前：??判断是否存在相同的档案：selectcount(*) FROM TA_ARCHIVE where archivetype=3and archiveno in (SELECTdistinct batchid FROM T_WS_DOCINDEX);??查询不同的batchid共43条，因此导入后将增加文书档案43条；SELECTcount(distinct batchid) FROM T_WS_DOCINDEX??因pdf命名与mdb表中的docname存在出入，执行以下语句使其保持一致select * from T_WS_DOCINDEX ws wherelength(docname)3;update t_ws_docindex set docname=lpad( docname,3,0) wherelength(docname)3??临时表处理select ws.* from t_ws_docindex ws orderby ws.batchid,ws.docname字段填充：---更新分类号为“A”update t_ws_docindex ws set ws.flh=A;update t_ws_docindex ws set ws.bmh=substr(ws.batchid,0,2);---更新到目录号update t_ws_docindex ws set ws.mlh=substr(ws.batchid,instr(ws.batchid,.,1,1)+1,instr(ws.batchid,-,1,2)-instr(ws.batchid,.,1,1)-1)select ws.* from t_ws_docindex ws where ws.mlh isnull---update archivetypeupdate t_ws_docindex ws set ws.archivetype=3;---update classidupdate t_ws_docindex ws set ws.classid=(select cls.classid from ta_classdef cls where cls.classcode=ws.bmh);---update catalognoupdate t_ws_docindex ws set ws.catalogno=ws.mlh;---update ajhupdate t_ws_docindex ws set ws.ajh=substr(ws.batchid,instr(ws.batchid,-,1,2)+1);select * from t_ws_docindex ws where ws.ajh isnull---update ndselectsubstr(ws.mlh,instr(ws.mlh,-,1,1)+1) from t_ws_docindex ws;update t_ws_docindex ws set ws.nd=substr(ws.mlh,instr(ws.mlh,-,1,1)+1);---查找不重复的编目号、目录号selectdistinct ws.bmh,ws.mlh from t_ws_docindex ws orderby ws.bmh---导入到 ta_catalognoselectdistinct cdf.classid,ws.mlh,1,1,1from t_ws_docindex ws join ta_classdef cdf on cdf.classcode=ws.bmh orderby cdf.classidinsertinto ta_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (selectdistinct ws.mlh,cdf.classid,1,1,1from t_ws_docindex ws join ta_classdef cdf on cdf.classcod