SQL Server批量插入

Question

我希望将一个只有一列的文本文件导入到我的SQL表中。这个文件只是一个脏话词汇列表。

我编写了以下TSQL来完成此操作：

BULK INSERT SwearWords
FROM 'c:\swears.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

然而，它报错了，“意外的文件结尾”。 我要导入的表只有一个标识字段，后面是我想插入文本的nvarchar字段。如果在每行开头添加“1，”，则可以正常工作，我认为这是因为SQL正在寻找2个字段。 有什么解决方法吗？

谢谢

- Gavin

---

你不想使用SSIS的原因是什么？ - Andriy Volkov

Answer 1

你需要使用FORMATFILE来完成此操作。请参见BULK INSERT。

FORMATFILE [ = 'format_file_path' ]

Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:

* The data file contains greater or fewer columns than the table or view.

* The columns are in a different order.

* The column delimiters vary.

* There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.

如需更详细的信息，请参见使用格式文件。

- D'Arcy Rittich

---

+1 有趣 - 谢谢！我之前不知道格式文件可以用于批量插入 - 这是个好建议，谢谢！ - marc_s

有没有一个针对具有不同列数插入的FORMATFILE示例？令人惊讶的是很难找到一个。 - Matthew Lock

Answer 2

这在有关BULK INSERT的在线书籍中描述为KEEPIDENTITY参数。这是它的含义：

保留标识列 指定导入文件中存在标识列的值。如果未给出KEEPIDENTITY，则忽略数据文件中此列的标识值，并且SQL Server会根据表创建期间指定的种子和增量值自动分配唯一值。如果数据文件不包含表或视图中标识列的值，请使用格式文件指定在导入数据时应跳过表或视图中的标识列；SQL Server会自动为该列分配唯一值。

因此，要么使用格式文件，要么提供虚拟值并确保不使用KEEPIDENTITY参数。

- SQLMenace

Answer 3

此外，您可以基于nvarchar列在表上创建视图，然后将数据批量插入到该视图中。这是一种非常干净的使用BULK INSERT的方式。

这样，您就不需要担心IDENTITY列或创建格式文件的问题了。

您的BULK INSERT语句应该如下所示：

BULK INSERT vw_SwearWords FROM 'c:\swearwords.txt' WITH (ROWTERMINATOR = '\n')

- Jesse

Answer 4

在 SQL Server 2008 中，我发现直接创建一个包含许多插入语句的文件，然后将其粘贴到管理工具的查询窗口中要比苦苦纠结于格式文件和批量插入文件权限更容易些。

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/

- Matthew Lock

Answer 5

你需要确保文本文件的结构和表格匹配 - 如果表格有两个字段，那么你也必须在文本文件中提供两个字段/列。

由于SQL表格中的第一列是一个IDENTITY字段，你可以提供任何值 - 但你必须在那里有一个值，我认为没有任何绕过这个问题的方法。

Marc

- marc_s

---

这不是与原帖写的完全一样吗？ - Stan R.

@Stan：我起初可以发誓这篇帖子没有包括“BULK”语句，只有“INSERT”——好吧，一定是我错过了。 - marc_s

Answer 6

检查最后一行是否有 CR/LF (\r\n)。有时这就是问题所在，而其他情况下可能是文件末尾多了一个回车符。你可以使用十六进制编辑器进行检查。

- Alan Featherston

Answer 7

在操作完成后，您可以删除标识列，并将其恢复。或者，如果这样会破坏您的数据库关系，您可以使用 DTS 或 SSIS 进行导入（如果只需要一次性导入） - 以便更细粒度地控制对列的修改。

- Rad