使用SQL高效地插入大量数据

Question

你好，我经常需要向表中插入大量数据。例如，我可能会从Excel或文本文件中获取数据，格式如下：

1,a
3,bsdf
4,sdkfj
5,something
129,else

在这个例子中，我经常构建6个插入语句并运行SQL脚本。当我必须向服务器发送数千个小数据包时，我发现这是慢的，它也会给网络带来额外的开销。

你有什么更好的方法吗？

更新：我正在使用ORACLE 10g。

- Dean

---

你只需要构建一个SQL插入语句，将所有行作为VALUES()，()项包含在其中。 - user207421

使用外部表来读取文本文件或CSV，并从外部表进行批量插入。 - Ollie

@EJP：Oracle不接受那种语法。 - Allan

可能是Filehelpers Excel to Oracle db的重复问题。 - APC

EJP，你能给我举个例子吗？ - Dean

Answer 1

使用Oracle外部表。

另见，例如：

• 关于外部表的OraFaq
• Tom对外部表的看法
• René Nyffenegger关于外部表的注释

下面是一个简单的例子，可以让您开始尝试

您需要一个位于服务器目录中的文件（熟悉目录对象）：

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 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的功能来访问数据：

SQL> select * from so13t order by data;

        ID DATA
---------- ------------------------------------------------------------
         1 a
         3 bsdf
       129 else
         4 sdkfj
         5 something

- user272735

---

优秀的手册 https://oracleappsnotes.wordpress.com/2012/02/10/oracle-external-tables-a-few-examples/ - zloctb

Answer 2

我不确定这是否适用于Oracle，但在SQL Server中，您可以使用BULK INSERT SQL语句从txt或csv文件上传数据。

BULK
INSERT [TableName]
FROM 'c:\FileName.txt'
WITH 
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
GO

确保表格列与txt文件中的内容匹配。对于更复杂的解决方案，您可能需要使用格式文件，请参见以下链接： http://msdn.microsoft.com/zh-cn/library/ms178129.aspx

- Aaron

Answer 3

有很多方法可以加快速度。

1）在单个事务中完成。这样可以避免连接的打开/关闭，从而提高速度。

2）直接加载CSV文件。如果将数据作为CSV文件加载，则根本不需要“SQL”语句。在MySQL中，“LOAD DATA INFILE”操作非常直观和简单。

3）您还可以将整个文件作为文本转储到名为“raw”的表中。然后让数据库使用触发器自行解析数据。这是一种hack方法，但它将简化应用程序代码并减少网络使用。

- jayunit100