使用SQL高效地插入大量数据 [英] Insert large amount of data efficiently with SQL
问题描述
1,a
3,bsdf的形式提供excel或文本文件中的数据
4,sdkfj
5,某事
129,else
那么我经常在这个例子中构造6个insert语句并运行SQL脚本。我发现这是很慢的,当我不得不发送数以千计的小包到服务器,这也造成额外的开销网络。
你最好的方法是什么? / p>
更新:我使用ORACLE 10g。
a href =http://download.oracle.com/docs/cd/E11882_01/server.112/e1650/tablecls.htm#CNCPT1141> Oracle外部表。
另见例如
一个简单的例子,让你开始
您需要位于服务器目录中的文件(熟悉目录对象):
SQL>从all_directories中选择directory_path,其中directory_name ='JTEST';
DIRECTORY_PATH
------------------------------------- -------------------------------------------
c:\ data\jtest
SQL> !cat〜/ .gvfs / jtest\ on\ on\ 192.168.xxx.xxx/exttable-1.csv
1,a
3,bsdf
4,sdkfj
5 ,
129,否则
创建外部表:
创建表so13t(
id number(4),
data varchar2(20)
)
organization外部(
类型oracle_loader
默认目录jtest / * jtest是现有的目录对象* /
访问参数(
记录由换行符分隔
由','
缺少字段值为null
)
位置('exttable-1.csv')/ *位于jtest目录中的文件* /
)
拒绝限制无限;
现在,您可以使用 SQL的所有权力访问数据:
SQL>从数据中选择*从so13t顺序;
ID DATA
---------- -------------------------- ----------------------------------
1 a
3 bsdf
129 else
4 sdkfj
5 something
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
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.
What's your best way of doing this?
Update: I'm using ORACLE 10g.
See also e.g.
- OraFaq about external tables
- What Tom thinks about external tables
- René Nyffenegger's notes about external tables
A simple example that should get you started
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:\data\jtest
SQL> !cat ~/.gvfs/jtest\ on\ 192.168.xxx.xxx/exttable-1.csv
1,a
3,bsdf
4,sdkfj
5,something
129,else
Create an external table:
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;
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高效地插入大量数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!