如何在 SQL Server 2005 和 BCP 9 中使用批量插入保存分号分隔文件中的数据？
这是我的示例数据：
1;a;b;c;; 2;d;e;f;; 3;g;h;i;;
4;j;k;l;; 5;m;n;o;;
6;p;q;r;;
这是我的示例格式文件（BCP 9）：
9.0
7
1 SQLCHAR 0 0 "" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 ";" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 ";" 3 s Latin1_General_CI_AS
4 SQLCHAR 0 0 ";" 4 t Latin1_General_CI_AS
5 SQLCHAR 0 0 ";" 5 u Latin1_General_CI_AS
6 SQLCHAR 0 0 ";" 6 v Latin1_General_CI_AS
7 SQLCHAR 0 0 "\r\n" 0 x Latin1_General_CI_AS
这是表结构：
create table bcp
(
id int identity,
i int,
s varchar(2),
t varchar(2),
u varchar(2),
v varchar(2),
dte datetime default getdate()
)
问题是，当我使用“\r\n”时，仅保存 1, 4, 6，而不是全部，但是当我尝试使用“\0”，仅保存第一条记录。 我要如何解决这个问题呢？
另一个问题，分号后空格的行终止符是什么？
Here is my sample data:
1;a;b;c;; 2;d;e;f;; 3;g;h;i;;
4;j;k;l;; 5;m;n;o;;
6;p;q;r;;
Here is my sample format file (BCP 9):
9.0
7
1 SQLCHAR 0 0 "" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 ";" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 ";" 3 s Latin1_General_CI_AS
4 SQLCHAR 0 0 ";" 4 t Latin1_General_CI_AS
5 SQLCHAR 0 0 ";" 5 u Latin1_General_CI_AS
6 SQLCHAR 0 0 ";" 6 v Latin1_General_CI_AS
7 SQLCHAR 0 0 "\r\n" 0 x Latin1_General_CI_AS
Here is the table structure:
create table bcp
(
id int identity,
i int,
s varchar(2),
t varchar(2),
u varchar(2),
v varchar(2),
dte datetime default getdate()
)
Problem is that when I used "\r\n", only 1, 4, 6 is save not all, but when I try to use "\0", only first record is save.
How I am gonna solve this problem?
Another Question, what is rowterminator for blank spaces after semicolon?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
asiaenforcer，很抱歉本周离线！
使用上面的数据，我想我已经明白了...
首先，我的目标表有问题 - 语句“CREATE TABLE [dbo].SansayRawInfo NOT NULL”不是正确的语法，因此从您以前的格式来看文件我相信您错过了一列（RawDataId），所以我想出的目标表是...
请注意
varchar 的长度为 5（基于
您的样本数据不是您的格式
文件）虽然这不应该使
因为你所有的长度都是不同的
更大（并且没有指定
您的创建表脚本）。
在我的脚本中因为
SQL_Latin1_General_CP1_CI_AS 是我的
数据库默认值，我只是在 SSMS 中编写了该表的脚本。 同样，这不会造成任何问题。
所以，我的下一步是根据您提供的示例原始数据浏览格式文件，我想出了以下格式文件...
我认为这里的关键位是最后一行中作为行分隔符的单个空格61 SQLCHAR 0 0 " " 0 Status ""
然后我运行了以下语句...
并且它正确导入了前 7 行。 最后一行未导入，因为在我的数据文件中没有像所有其他行上那样的尾随空格行终止符。 当我将最后一个空格添加到数据文件中时，我得到了全部 8 行。 如果您的源数据是系统生成的，我希望它能够打印出尾随空格 - 如果不是，您需要重新访问源数据，因为 SQL Server 要求它在整个数据集中保持一致，并且可能会错过不一致的行。
最后，我的结果数据如下所示...
请注意
我的 RawDataId 从 8 开始，因为该表之前已被清除并重新填充。
希望这会有所帮助，并且您不必重新访问源数据，因为这可能是真正的痛苦:)
asiaenforcer, my apologies for being off line for week!
Using your data above I think I've got it...
Firstly I had a problem with your destination table - the statement "CREATE TABLE [dbo].SansayRawInfo NOT NULL," is not the correct syntax so from looking at your previous format file I believe you missed out a column (RawDataId) so the destination table I came up with is...
PLEASE NOTE
varchars are length of 5 (based on
your sample data NOT your format
file) although this should not make a
difference as all of your lengths are
greater (and none were specified in
your create table script).
in my script because
SQL_Latin1_General_CP1_CI_AS is my
database default and I just scripted the table in SSMS. Again this should not cause any problems.
So, my next step was to go through the format files based on the sample raw data you supplied and I came up with the following format file...
I think the key bit here is the single space as a line delimiter in the last line 61 SQLCHAR 0 0 " " 0 Status ""
I then ran the below statement...
And it imported the first 7 rows correctly. The last row did not import because in my data file there was not a trailing space line terminator as there were on all of the other rows. When I added in this final space to the data file I got all 8 rows. If your source data is system generated I would hope that it will print out the trailing space - if not you need to revisit your source data because SQL Server requires it to be consistent across the whole set and may miss out inconsistent rows.
In the end my result data looked like the below...
PLEASE NOTE
My RawDataId's start at 8 because the table was previously cleared and repopulated.
Hope this helps somewhat and that you do not have to revisit your source data because that can be right pain :)
好的，我让它为我工作。 我使用了以下...
数据文件（基于您的示例）...
每行都以返回字符终止 - 不是像您的示例中那样的连续行
然后使用格式文件...
并获得结果集.. 我相信
这里的关键是如何跳过目标表中的标识字段和默认值的列。 这里有一篇很好的 MSDN 文章
OK, I got this to work for me. I used the following...
Data file (based on your example)...
Each line was terminated with a return char - not a continuous line as in your example
Then used the format file...
And got the result set...
I believe the key here is in how to skip the columns for the identity field and the default value in your destination table. There is a good MSDN article on this here