SQL Server批量插入 [英] SQL Server Bulk Insert

问题描述

我想将一列文本文件导入到我的一个sql表中。该文件只是一个脏话列表。

I want to import a one column text file into one of my sql tables. The file is just a list of swear words.

我编写了以下TSQL来做到这一点

I've written the following TSQL to do this

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

但是由于未预期的文件结尾而出错。我要导入的表只是一个标识字段，后跟一个我要在其中插入文本的nvarchar字段。如果我将文本文件 1添加到eveyr行的开头，则效果很好，我认为这是因为SQL如果查找2个字段。

However it errors with unexapected end of file. The table im importing to is just an identity field followed by a nvarchar field that I want to insert the text into. It works fine if I add in the text file "1," to the beginning of eveyr line, I assume this is because SQL if looking for 2 fields. Is there any way around this?

谢谢

推荐答案

为此使用FORMATFILE。请参见批量插入。

You need to use FORMATFILE for this. See BULK INSERT.

FORMATFILE [='format_file_path']

FORMATFILE [ = 'format_file_path' ]

指定
格式文件的完整路径。格式文件描述了数据
文件，该文件包含使用bcp实用程序在
相同表或视图上创建的存储响应
。在以下情况下应使用格式文件
：

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.

有关更多详细信息，请参见使用格式文件。

For more detailed information, see Using Format Files.

这篇关于SQL Server批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！