【干货攻略】达梦数据库外部表插入数据

利用外部表的特性将文本数据插入到表中。

CREATE OR REPLACE DIRECTORY "EXTDIR_2" AS 'd:\ext_table_2';

CREATE TABLE EXT_TABLE0 ( C1 INT, C2 INT, C3 INT ) ;

create or replace procedure imp_ext_table(owner varchar(500), tname varchar(500),dataStr varchar(500)) as
sqlstr varchar;
begin
sqlstr='';
select 'CREATE EXTERNAL TABLE if not exists ' || owner || '.EXT_' || tname || substr(tabledef(owner,tname),
instr(tabledef(owner,tname),'('),
instr(tabledef(owner,tname),'STORAGE')-instr(tabledef(owner,tname),'(')-1) || ' FROM DATAFILE DEFAULT DIRECTORY EXTDIR_2 LOCATION(' || '''' || dataStr || ''')' ||
' PARMS(FIELDS DELIMITED BY ''|'', RECORDS DELIMITED BY 0x0d0a)'
into sqlstr from dual;
execute immediate sqlstr;
execute immediate 'insert into ' || owner || '.' || tname || ' select * from '|| owner ||'.EXT_' || tname;
commit;
execute immediate 'drop table ' || owner ||'.EXT_' || tname;
end;

call imp_ext_table(user,‘EXT_TABLE0’,‘data.txt’);

select * from EXT_TABLE0;

以上为本期分享，希望能带给大家帮助。想要了解更多往期干货，可访问页面最下方#达梦技术干货攻略#合集或下方相关分享。在此邀请更多学员参与“达梦技术干货投稿活动”，稿件获选后将在达梦“干货分享”专栏进行发布，欢迎来稿！