关于批量插入问题 [英] On to Bulk Insert issues
问题描述
好的，我试过了：
USE Alert_db;
BULK INSERT资金来自''C：\\data \\ \\\myData.dat''
WITH（FIELDTERMINATOR =''\ t''，
KEEPNULLS，
ROWTERMINATOR ='' \\'n'）;
我收到以下错误。
Msg 4864，Level 16，State 1，Line 3
第1行第4列（f_asset_classes_id）的批量加载数据转换错误（类型不匹配或指定代码页的
无效字符）。
Msg 4866，Level 16，状态8，第3行
批量加载失败。行的数据文件中的列太长了
1，第6列。验证字段终止符和行终止符是否正确指定了
。
消息7399，级别16，状态1，行3
OLE DB提供程序BULK对于链接服务器（null）报了一个
错误。提供商没有提供有关错误的任何信息。
消息7330，级别16，状态2，行3
无法从OLE DB提供程序BULK获取行。对于链接服务器
"（null）"。
我遇到的一个限制是，似乎没有办法来
告诉MS SQL Server这些字段可选地用引号括起来。
也就是说，文本字段用引号括起来，而例如，数字
字段，不是，并且这些可选引号不包含在字段数据中的
。
我不知道状态1是什么vsState 8应该是这个意思。
这个例子中的表允许多列中的空值，
和平面文件中的空值由连续的标签表示。可能
这会导致Bulk Insert语句出现问题吗？
在其他情况下，我必须使用像批量插入这样的东西，
涉及包含日期的多个列。我知道MS SQL支持文件中使用的
格式（通过阅读cast for
操作的文档），但是有一种简单的方法可以告诉MS SQL哪个是
支持在读取此数据时使用的日期格式。我读过bcp
应该对此有用，但我还没弄明白。
在我加载的大约一半的情况下来自文件的数据，首次创建数据库时，数据加载一次，而在其余的数据中，每个工作日都要加载新数据。所以我需要能够从命令行提交所需的命令，然后使用perl脚本调用它。
。
顺便说一句：我订购了几本关于T-SQL的书，但他们还没有给b $ b抵达。
谢谢
Ted
OK, I tried this:
USE Alert_db;
BULK INSERT funds FROM ''C:\\data\\myData.dat''
WITH (FIELDTERMINATOR=''\t'',
KEEPNULLS,
ROWTERMINATOR=''\r\n'');
And I got the following errors.
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for
the specified codepage) for row 1, column 4 (f_asset_classes_id).
Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row
1, column 6. Verify that the field terminator and row terminator are
specified correctly.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an
error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server
"(null)".
One limitation I encountered is that there doesn''t seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.
I do not know what "State 1" vs "State 8" is supposed to mean.
The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?
In other cases, where I have to use something like bulk insert,
involves several columns containing dates. I know MS SQL supports the
format used in the file (by reading the documentation for cast
operations), but is there an easy way to tell MS SQL which of the
supported date formats to use when reading this data. I''ve read bcp
should be useful for this, but I have yet to figure that out.
In about half of the cases where I load data from a file, the data is
loaded once when the database is first created, and in the rest, there
is new data to be loaded every business day; so I need to be able to
submit the required command from the command line, and thus invoke it
using a perl script.
BTW: I have ordered a couple books on T-SQL, but they have yet to
arrive.
Thanks
Ted
推荐答案
Ted（r.*********@rogers.com）写道：
Ted (r.*********@rogers.com) writes:
我遇到的一个限制是，似乎没有办法将
告诉MS SQL Server这些字段可选地用引号括起来。
也就是说，文本字段用引号括起来，例如，数字
字段，不是，并且这些可选引号不是在字段中的数据中包含
。
One limitation I encountered is that there doesn''t seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.
这是正确的，如果可选的话只是可选的，这样你就可以得到
：
9;一些未引用的数据; 12; 9.234; 2004-12-12
19;一些引用数据; - 12; 31.4; 2003-02-23
但是如果一直引用文本列，您可以使用
格式文件来处理它，您可以在其中指定每个字段。适合上面示例中
第二行的格式文件可能如下所示：
5
1 SQLCHAR 0 0" ;; \"" 1 col1""
2 SQLCHAR 0 0" \";" 2 col2""
3 SQLCHAR 0 0" ;;" 3 col3"
4 SQLCHAR 0 0" ;;" 4 col3""
5 SQLCHAR 0 0" \\ n" 5 col3""
第一行是文件格式的版本。接下来是文件中
字段的数量。以下行分别描述了一个字段。
第一列是记录号。第二列是文件中字段
的数据类型。对于文本文件，对于Unicode文件，它总是SQLCHAR或总是SQLNCHAR
。其他数据类型仅用于二进制格式。
第三列是前缀长度，仅用于二进制文件。第四个
列是长度，用于固定长度字段。第五个字段
是终止符，在这里你指定引号。
六列是数据库列，1表示第一列。 0
表示不导入此字段。第七列是
列名，但它是信息性的。 BCP / BULK INSERT不使用它。
最后一个colunm是文件中数据的整理。
总的来说，请记住BCP / BULK INSERT读取二进制文件，
行终止符实际上只是最后一个字段的终止符。
That''s correct, if optionally means just optionally, so that you
could have:
9;Some unquoted data;12;9.234;2004-12-12
19;"Some quoted data";-12;31.4;2003-02-23
But if a text column is consistently quoted, you can handle this with a
format file where you specify each field. A format file that fits the
second row in the example above could look like:
8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""
The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.
First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.
The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.
Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it''s informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.
Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.
我不知道是什么"州1 vsState 8应该是的意思。
I do not know what "State 1" vs "State 8" is supposed to mean.
您可以将其视为白噪声。状态编号可能会告诉SQL Server开发人员一些东西，但是没有记录。
You can consider it as white noise. The state number may tell the
SQL Server developers something, but they are not documented.
此示例中的表格允许空值在几列中，
和平面文件中，空值由连续的选项卡表示。可能
这会导致Bulk Insert语句出现问题？
The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?
这应该可以正常工作。但是，如果缺少字段，那么你就可以在一行上有六个字段，而在下一行有八个字段，你输了。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
在线预订SQL Server 2005
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons /books.mspx
That should work fine. However, if fields are missing, so that you
have six fields on one line, and eight on the next, you lose.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
大家好......
我有机会打开这样的话题:)）
所以我可以问一些我需要知道的东西...
我正在使用BCP命令将文本文件导入SQL Server 2000
但我收到错误消息像这样的ge：
字符串数据，右截断
我不知道我将如何克服这个问题!!
在什么情况下sql server 2000会返回这样的错误？
我应该怎么做才能解决这个问题？
非常感谢
Tunc Ovacik
**************** ********************************** **************** *
Erland Sommarskog写道：
hi all again...
it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...
well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"
and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?
what should i do to get over this problem?
thanks a lot
Tunc Ovacik
************************************************** *****************
Erland Sommarskog wrote:
Ted（r.*********@rogers.com ）写道：
Ted (r.*********@rogers.com) writes:
我遇到的一个限制是，似乎没有办法让b / b $ b告诉MS SQL Server字段可选地用引号括起来。
也就是说，文本字段用引号括起来，例如，数字
字段，不是，并且这些可选引号不是在字段中的数据中包含
。
One limitation I encountered is that there doesn''t seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.
这是正确的，如果可选的话只是可选的，这样你就可以得到
：
9;一些未引用的数据; 12; 9.234; 2004-12-12
19;一些引用数据; - 12; 31.4; 2003-02-23
但是如果一直引用文本列，您可以使用
格式文件来处理它，您可以在其中指定每个字段。适合上面示例中
第二行的格式文件可能如下所示：
5
1 SQLCHAR 0 0" ;; \"" 1 col1""
2 SQLCHAR 0 0" \";" 2 col2""
3 SQLCHAR 0 0" ;;" 3 col3"
4 SQLCHAR 0 0" ;;" 4 col3""
5 SQLCHAR 0 0" \\ n" 5 col3""
第一行是文件格式的版本。接下来是文件中
字段的数量。以下行分别描述了一个字段。
第一列是记录号。第二列是文件中字段
的数据类型。对于文本文件，对于Unicode文件，它总是SQLCHAR或总是SQLNCHAR
。其他数据类型仅用于二进制格式。
第三列是前缀长度，仅用于二进制文件。第四个
列是长度，用于固定长度字段。第五个字段
是终止符，在这里你指定引号。
六列是数据库列，1表示第一列。 0
表示不导入此字段。第七列是
列名，但它是信息性的。 BCP / BULK INSERT不使用它。
最后一个colunm是文件中数据的整理。
总的来说，请记住BCP / BULK INSERT读取二进制文件，
行终止符实际上只是最后一个字段的终止符。
That''s correct, if optionally means just optionally, so that you
could have:
9;Some unquoted data;12;9.234;2004-12-12
19;"Some quoted data";-12;31.4;2003-02-23
But if a text column is consistently quoted, you can handle this with a
format file where you specify each field. A format file that fits the
second row in the example above could look like:
8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""
The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.
First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.
The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.
Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it''s informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.
Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.
我不知道是什么"州1 vsState 8应该是的意思。
I do not know what "State 1" vs "State 8" is supposed to mean.
您可以将其视为白噪声。状态编号可能会告诉SQL Server开发人员一些东西，但是没有记录。
You can consider it as white noise. The state number may tell the
SQL Server developers something, but they are not documented.
此示例中的表格允许空值在几列中，
和平面文件中，空值由连续的选项卡表示。可能
这会导致Bulk Insert语句出现问题？
The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?
这应该可以正常工作。但是，如果缺少字段，那么你就可以在一行上有六个字段，而在下一行有八个字段，你输了。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书 http://www.microsoft.com/sql/prodinf ... ons / books.mspx
惊恐发作（tu*********@gmail.com）写道：
panic attack (tu*********@gmail.com) writes:
我有机会打开这样一个话题:)）
所以我可以问一些我需要知道的事情......
我正在使用BCP命令得到一个文本文件到SQL Server 2000
但我得到一个像这样的错误信息：
"字符串数据，右截断"
，我不知道我将如何克服这个问题!!
在什么情况下sql server 2000会返回这样的错误？
我该怎么做才能克服这个问题？
it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...
well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"
and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?
what should i do to get over this problem?
首先开始一个新线程，这样我们可以分开不同的问题。
您可以尝试使用-e选项获取文件错误，然后您将看到文件中哪个记录引发此错误的
记录。然而，我需要添加警告
并非所有错误都列在错误文件中，而且我不记得是否
此错误获取列表。
错误的两个最合理的原因是
1）文件的数据不符合表格列。
2）那里你的分隔符是一个错误，因此BCP不同步。
如果这没有帮助，请发布表格的CREATE TABLE命令和
您正在使用的BCP的确切命令行。如果您使用格式文件，请
还包括格式文件。最后，包括输入文件的样本。
最好的是样本是否产生错误消息。如果输入文件
的长度超过80个字符，请将其作为附件包含在内，这样就可以确保
在新闻传输中不会被破坏。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
联机丛书SQL Server 2000
http：// www。 microsoft.com/sql/prodinf...ons/books.mspx
First start a new thread, so we can keep different problems apart.
You can try the -e option to get errors to a file, you will then see which
records in the file that provokes this error. I need however add the caveat
that not all errors get listed in the error file, and I don''t remember if
this error gets lists.
The two most plausible reasons for the error is
1) the file has data that does not fit the table columns.
2) there is an error with your delimiters, so that BCP gets out of sync.
If that does not help, post the CREATE TABLE command for the table and the
exact command line for BCP you are using. If you use a format file, please
also include a format file. Finally, include a sample of the input file.
Best is if the sample produces the error message. If the input file
exceeds 80 characters in length include it as an attachment, so it does
not get wrecked in news transport.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这篇关于关于批量插入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
