问题描述
您好，我经常需要向表中插入大量数据.例如，我会以
Hi I often have to insert a lot of data into a table. For example, I would have data from excel or text file in the form of
1,a
3,bsdf
4,sdkfj
5,something
129,else
然后我在这个例子中经常构造6条insert语句并运行SQL脚本.我发现当我必须向服务器发送数千个小包时，这很慢，而且还会给网络带来额外的开销.
then I often construct 6 insert statements in this example and run the SQL script. I found this was slow when I have to send thousands of small packages to server, it also causes extra overhead to the network.
你最好的方法是什么?
更新:我使用的是 ORACLE 10g.
Update: I'm using ORACLE 10g.
推荐答案
使用 Oracle 外部表.
另见例如
- OraFaq 关于外部表
- Tom 对外部表的看法
- René Nyffenegger 关于外部表的说明
一个可以帮助您入门的简单示例
您需要一个位于服务器目录中的文件(熟悉目录对象):
You need a file located in a server directory (get familiar with directory objects):
SQL> select directory_path from all_directories where directory_name = 'JTEST';
DIRECTORY_PATH
--------------------------------------------------------------------------------
c:datajtest
SQL> !cat ~/.gvfs/jtest on 192.168.xxx.xxx/exttable-1.csv
1,a
3,bsdf
4,sdkfj
5,something
129,else
创建外部表:
create table so13t (
id number(4),
data varchar2(20)
)
organization external (
type oracle_loader
default directory jtest /* jtest is an existing directory object */
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('exttable-1.csv') /* the file located in jtest directory */
)
reject limit unlimited;
现在您可以使用SQL 的所有功能来访问数据:
Now you can use all the powers of SQL to access the data:
SQL> select * from so13t order by data;
ID DATA
---------- ------------------------------------------------------------
1 a
3 bsdf
129 else
4 sdkfj
5 something
这篇关于用SQL高效插入大量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！