SQL Server 2008中的批量更新
我有一个应用程序在一家拥有总部和分支机构的企业上运行。每个分支机构都有独立的本地服务器和数据库。主服务器和数据库每天通过批量更新进行更新。在这种情况下我该如何克服身份冲突。
例如：我在分支 A 上有一条 ID 为 10 的记录 还有一个引用 id 10（外键）的事务，但是在将其保存到主数据库中时，唯一的 id 可能会更改，因此事务不匹配......
请帮助我解决这个问题......
提前感谢大家....
I have an application run on an enterprise having head office and branches. Each branch has separate local server and DB. The master server and DB is updated daily by batch updation . How can i overcoming the id conflict in such situation.
Eg: i have a record with ID 10 on branch A
Also a transaction with reference id 10(foreign key) but while saving this into master db the unique id may be changed and hence the transaction is mismatched....
Pls help me for solving this...
Advance thanks to all....
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我现在会忘记技术方面并澄清业务/应用程序逻辑。从您的帖子中根本不清楚哪里可能会出现冲突：在客户记录上，在交易上，还是在所有数据上？为什么会产生冲突？冲突如何解决？当您准确了解数据发生的情况以及业务期望发生的情况时，您就可以研究技术解决方案。
GUID、数字范围和映射表等建议非常有用，但根本不清楚您要解决什么问题以及业务解决方案是什么。将所有数据物理地放到一个地方很容易（SSIS 是一个很好的起点），真正的问题是您遵循什么规则来合并数据？
I would forget about the technical aspects for now and clarify the business/application logic. It's not at all clear from your post where the conflicts can arise: on a customer record, on a transaction, on all data? Why do conflicts arise? How are conflicts resolved? When you understand exactly what's happening with the data and what the business expects to happen, then you can look into technical solutions.
Suggestions such as GUIDs, number ranges and mapping tables are very useful, but it isn't at all clear what problem you're trying to solve and what the business solution is. Getting all the data physically into one place is easy (SSIS would be a good place to start), the real question is what rules do you follow to merge the data?
不知道如何进行批量更新或数据库有多大以及有多少表面临此类问题的详细信息，一种可能的通用方法是维护映射表
在批量更新逻辑中，
对于新记录，您将首先将记录插入到主表中，然后使用从主表生成的新 ID，将记录插入到映射表中，其中包含以下详细信息：分支、分支 ID、MasterID，其中存储新记录的 2 个 ID 之间的关系
对于现有记录，您将连接映射表并使用映射表中的 masterID 更新记录
通常，在 SQL Server 中，如果批量更新是一项作业，则可以使用 SSIS 并在 SSIS 包中包含这种逻辑。
此外，这取决于您的设计，但通常有一个
源数据库 -->暂存数据库 --> 在您的情况下，目标数据库
映射到
分支数据库 -->暂存数据库 --> 数据库
掌握用于此类 ETL 类型任务的 。在这种情况下，映射表可能应该驻留在临时数据库中，但如果您没有这样的设计（您确实应该），这些映射表也可以驻留在目标数据库中，即主数据库中
Not knowing the details of exactly how you do your batch updation or how big your database is and how many tables you have which face this kind of issue, one generic way that is possible way is by maintaining mapping tables
In your batch updation logic,
for new records, you will first insert the records into the master table and then using their new ID's generated from master, insert a record into mapping table with details like eg: branch, branchID, MasterID which stores the relation between the 2 ID's
for existing records, you will join with the mapping table and update the records using their masterID's from the mapping table
Usually, in SQL Server, if the batch updation is a job, you can use SSIS and have this kind of logic within your SSIS package.
Additionally, it depends on your design but there is usually a
Source DB --> Staging DB --> Destination DB
in your case which maps to
Branch DB --> Staging DB --> Master DB
for such ETL kind of tasks. In such cases, the mapping tables should probably reside in the Staging Database but if you do not have such a design (which you really should ) these mapping tables can reside in Destination i.e. Master database as well
为了克服这个问题，您可以使用
uniqueidentifier作为分支ID的数据类型。因此，每个数据库都可以使用
NEWID()创建 ID，而不必担心主数据库更改密钥，因为它是普遍唯一的。如果您不想更改数据类型，则每个分支可能有数字范围。取决于每个分支中创建的记录数。
For this to overcome you could use
uniqueidentifieras the datatype for the branchID.So each database can create the ID with
NEWID()without having to worry about the master database changing the key because is it universaly unique.If you dont want to change the datatype you might have number ranges for each branch. Depending on the number of records that are created in each branch.