SQL Server上的BCP顺序 [英] BCP Order on SQL Server
问题描述
我有两台SQL Server 2000计算机。同一个文件每晚发送到每个服务器
，存储过程使用BULK INSERT将其加载到
临时表中进行处理。
一旦我把它打包进去，我就把它放到临时表中，附上一个IDENTITY
列。 （我需要将此标识栏稍后分组
以删除重复项。）
ie
select节奏。*，
IDENTITY（int，1,1）AS ID_Num
到＃test1
来自tempExtract节奏
我的问题是：我可以期望ID_Num和相应的
行复制到表中的文件在每台服务器上都是一样的吗？即，每个BCP进入临时表的
在两个
服务器上以相同的顺序发生，因为该文件，BULK INSERT命令和索引
在每台服务器上都是相同的。
Thomas Richards（to**********@rocketmail.com）写道：我有两台SQL Server 2000计算机。每晚向每个服务器发送相同的文件，存储的proc使用BULK INSERT将其加载到
临时表中进行处理。
一旦我bcp''编辑它，我把它放入一个附有IDENTITY
列的临时表中。 （我需要将此标识栏稍后分组才能删除重复项。）
选择速度。*，
IDENTITY（int， 1,1）AS ID_Num
进入#test1
来自tempExtract节奏
我的问题是：我可以期待ID_Num和相应行的
文件被复制到每个服务器上的表是一样的吗？即，每个BCP进入临时表在两个服务器上以相同的顺序发生，因为每个服务器上的文件，BULK INSERT命令和索引
相同。
不，你需要在你加载的表上加上
文件的标识列。我不确定您是否可以信任IDENTITY
值来准确匹配输入文件，如果它有效，很可能只需机会就可以获得
。也就是说，微软没有承诺它应该工作，它可能会在未来的SQL版本中发生变化
服务器。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog< es **** @ sommarskog.se>在消息新闻中写道：< Xn ********************** @ 127.0.0.1> ...Thomas Richards（ to**********@rocketmail.com）写道：我有两台SQL Server 2000机器。每晚向每个服务器发送相同的文件，存储的proc使用BULK INSERT将其加载到
临时表中进行处理。
一旦我bcp''编辑它，我把它放入一个附有IDENTITY
列的临时表中。 （我需要将此标识栏稍后分组才能删除重复项。）
选择速度。*，
IDENTITY（int， 1,1）AS ID_Num
进入#test1
来自tempExtract节奏
我的问题是：我可以期待ID_Num和相应行的
文件被复制到每个服务器上的表是一样的吗？即，每个BCP进入临时表在两个服务器上以相同的顺序发生，因为每个服务器上的文件，BULK INSERT命令和索引
相同。
不，你需要在你加载文件的桌子上加上标识栏。我不确定你是否可以信任IDENTITY
值来完全匹配输入文件，如果它有效，很可能仅仅是机会。也就是说，Microsoft没有承诺它应该可以工作，并且它可能会在SQL
Server的未来版本中发生变化。
感谢那。我正试图解决的问题是，我需要一个关键字段，然后是一个或多个地址。构成地址的关键字段和
字段都是字符/变量字符。我想选择
一个任意地址与关联密钥并放入另一个
表。没有业务规则（例如总是带有
最低街道号码的那个），它总是只能识别一个
地址。最初，我认为按键和行号分组并选择
最低号码的那个。我更愿意这样做，因为它会获得文件中的第一个条目，这很可能会给我更好的地址详细信息
。然而，正如你已经指出的那样，当bcp'进入时，我不能再接受订单。有没有其他方法可以做到这一点
或者我必须在SQL Server
处理之前将行号添加到文件中吗？
Thomas Richards（到******* ***@rocketmail.com）写道：谢谢你。我试图解决的问题是，我有一个关键字段，然后是一个或多个地址。构成地址的关键字段和
字段都是字符/变量字符。我想选择一个任意地址与关联密钥并放入另一个
表。没有业务规则（例如总是带有
最低街道号的那个），它总是只能识别一个
地址。最初，我认为按键和行号分组，然后选择编号最小的那个。我更愿意这样做，因为它会获得文件中的第一个条目，这很可能会给我更好的地址详细信息。然而，正如你已经指出的那样，当bcp'进入时，我不能依赖于顺序。还有其他方法可以做到这一点
或者我是否必须添加行号SQL Server
处理之前的文件？
唯一可以确定的是自己添加行号。这可以通过两种方式完成：1）通过运行程序来操作文件
添加行号。 2）使用程序
插入数据，而不是写入文件。实际上，您仍然可以使用批量加载，但是您可以使用BCP API从变量批量生成
。
但是，BULK INSERT到表中一个IDENTITY给你相当不错的
赔率，据我了解你的情况，它似乎不是一场灾难，如果数字不是你所期望的那么
。所以我会去那。
-
Erland Sommarskog，SQL Server MVP， esvis@sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books.asp
I have two SQL Server 2000 machines. The same file is sent nightly to
each server and a stored proc uses BULK INSERT to load it into a
staging table for processing.
Once I''ve bcp''ed it in, I put it into a temp table with an IDENTITY
column appended to it. (I need this identity column to group by later
on to remove duplicates.)
ie
select tempo.*,
IDENTITY(int, 1,1) AS ID_Num
into #test1
from tempExtract tempo
My question is : can I expect the ID_Num and the corresponding line of
the file copied to the table to be the same on each server? Ie will
each BCP into the staging table occur in the same order on both
servers given that the file, the BULK INSERT command and the indexes
are the same on each server.
Thomas Richards (to**********@rocketmail.com) writes:I have two SQL Server 2000 machines. The same file is sent nightly to
each server and a stored proc uses BULK INSERT to load it into a
staging table for processing.
Once I''ve bcp''ed it in, I put it into a temp table with an IDENTITY
column appended to it. (I need this identity column to group by later
on to remove duplicates.)
ie
select tempo.*,
IDENTITY(int, 1,1) AS ID_Num
into #test1
from tempExtract tempo
My question is : can I expect the ID_Num and the corresponding line of
the file copied to the table to be the same on each server? Ie will
each BCP into the staging table occur in the same order on both
servers given that the file, the BULK INSERT command and the indexes
are the same on each server.
No, you would need to have the identity column on the table you load
the file into. I don''t know for sure that you can trust the IDENTITY
value to match the input file exactly, and if it works, it is likely
to by mere chance. That is, there is no committment from Microsoft
that it should work, and it could change in a future version of SQL
Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...Thomas Richards (to**********@rocketmail.com) writes:I have two SQL Server 2000 machines. The same file is sent nightly to
each server and a stored proc uses BULK INSERT to load it into a
staging table for processing.
Once I''ve bcp''ed it in, I put it into a temp table with an IDENTITY
column appended to it. (I need this identity column to group by later
on to remove duplicates.)
ie
select tempo.*,
IDENTITY(int, 1,1) AS ID_Num
into #test1
from tempExtract tempo
My question is : can I expect the ID_Num and the corresponding line of
the file copied to the table to be the same on each server? Ie will
each BCP into the staging table occur in the same order on both
servers given that the file, the BULK INSERT command and the indexes
are the same on each server.
No, you would need to have the identity column on the table you load
the file into. I don''t know for sure that you can trust the IDENTITY
value to match the input file exactly, and if it works, it is likely
to by mere chance. That is, there is no committment from Microsoft
that it should work, and it could change in a future version of SQL
Server.
Thanks for that. The problem that I''m trying to get round is that I
have a key field and then one or more addresses. The key field and the
fields that make up the address are all chars/varchars. I want to pick
one arbitrary address to associate with the key and put in another
table. There are no business rules (eg always take the one with the
lowest street number) that will always identify just one of the
addresses. Originally, I thought group by key and line number and pick
the one with the lowest number. I would prefer to do this as it would
get the first entry in the file which would more than likely give me
the better address details. However as you''ve pointed out I can''t
depend on the order when bcp''ed in. Is there any other way to do this
or would I have to get line number added to the file before SQL Server
processes it?
Thomas Richards (to**********@rocketmail.com) writes:Thanks for that. The problem that I''m trying to get round is that I
have a key field and then one or more addresses. The key field and the
fields that make up the address are all chars/varchars. I want to pick
one arbitrary address to associate with the key and put in another
table. There are no business rules (eg always take the one with the
lowest street number) that will always identify just one of the
addresses. Originally, I thought group by key and line number and pick
the one with the lowest number. I would prefer to do this as it would
get the first entry in the file which would more than likely give me
the better address details. However as you''ve pointed out I can''t
depend on the order when bcp''ed in. Is there any other way to do this
or would I have to get line number added to the file before SQL Server
processes it?
The only way to be sure is to add the line numbers yourself. This can be
done in two ways: 1) Manipulate the file, by running it through a program
that adds a line number. 2) Instead of writing a to file, have the program
to insert the data. In fact, you can still use bulk load, but you would
bulk from variables, using the BCP API.
However, BULK INSERT into a table with an IDENTITY gives you fairly good
odds, and as I understand your case, it does not seem to be a disaster,
if number is not what you expect. So I would go for that.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于SQL Server上的BCP顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
