SQL Server 中 4 列的一种同步
这是我的问题：
我有一个旧数据库，没有任何限制。我将一些表从旧数据库复制到新数据库。这个副本很简单，我每天晚上都会在作业中运行它。
在我的新数据库（很好地带有约束）中，我将所有这些松散表与主表进行约束。所有这些表都有一个由 3ID 和一个字符串组成的键。
我的主表会将这些 3ID 和字符串转换为 1 ID，因此该表将有 5 列。
在松散表中，某些记录可能是双倍的，因此要在主表中插入 ID，我将采用 3 个 ID 和一个字符串的不同值，并将它们插入到我的主表中。
旧数据库中的表每天都会更新。该副本每天运行，并且我想从主表中与复制的表建立一对多关系。
这给我带来了问题：
如何更新主表，对已插入的键不执行任何操作并添加新键？如果在旧数据库中删除旧密钥，请勿删除旧密钥。
我正在考虑对旧数据库中的所有键创建一个清晰的视图，但如何将其更新到主表？这需要在其他表的每日副本之前运行（否则会因约束而失败）
另一个想法是在我的网站中的 Linq-to-SQL 中运行主表的更新，但这似乎不太好干净的。
简而言之：
- 旧数据库是 SQL Server 2000
新数据库是 SQL Server 2008
旧数据库没有约束，一些表的复制每天都会发生。
应该有一个主表，将 3ID 和 1string 键转换为 1ID 键，并对其他表进行约束。
主表应在复制作业之前更新，否则约束将失败。主表将由 1 个表的几列组成。这个不同的将在旧数据库的视图中。
只能在主数据库中添加新行
有人有一些想法，一些指导吗？
可视化数据库：
这些松散的表是有关公司的详细信息。 1 个表有地址 1 个表有联系人，另一个表有我们系统的用户名和登录名（对于 1 个公司来说可能超过 1 个） 公司由 3ID 和 1string 标识。主表将列出这些唯一的 ID 和字符串，以便将它们转换为 1 个 ID。然后，这个 1 ID 将在我的数据库的其余部分中使用。然后将从主表到所有这些松散表建立一对多关系。我希望这能澄清一点:)
This is my problem:
I have an old database, with no constraints whatsoever. There are a handful of tables I copy from the old database to the new database. This copy is simple, and I'm running that daily at night in a Job.
In my new database (nicely with constraints) I made all these loose tables in constraint with a main table. all these tables have a key made of 3ID's and a string.
My main table would translate these 3ID's and a string to 1 ID, so this table would have 5 columns.
In the loose tables, some records can be double, so to insert the ID's in the main table I'd take a distinct of the 3 ID's and a string, and insert those into my main table.
The tables in the old database are updated daily. the copy is run daily, and from the main table I'd like to make a one-to-many relation with the copied tables.
This gives me the problem:
how do I update the main table, do nothing with the already-inserted keys and add the new keys? Don't remove old keys if removed in old database.
I was thinking to make a distinct view of all keys in the old database, but how would I update this to the main table? This would need to run before the daily copy of the other tables (or it would fail on the constraints)
One other idea is to run this update of the main table in Linq-to-SQL in my website but that doesn't seem very clean.
So in short:
- Old DB is SQL Server 2000
New DB is SQL Server 2008
Old db has no constraints, copy of some tables happens daily.
There should be a Main table, translating the 3ID and 1string key to a 1ID key, with a constraint to the other tables.
The main table should be updated before the copy job, else the constraints will fail. The main table will be a distinct of a few columns of 1 table. This distinct will be in a view on the old db.
Only new rows can be added in the main db
Does anyone have some ideas, some guidance?
Visualize the DB:
these loose tables are details about a company. 1 table has address(es) 1 table has contact person, another table has it's username and login for our system (which could be more than 1 for 1 company)
a company is identified by the 3ID's and 1string. the main table would list these unique ID's and string so that they could be translated to 1 ID. this 1 ID is then used in the rest of my DB. the one to many relation would then be made from the main table to all those loose tables. I hope this clears it up a bit :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为您可以使用 EXCEPT 插入主表中尚未存在的 id http://msdn.microsoft.com/en-us/library/ms188055.aspx
例如：
I think you could use EXCEPT to insert the ids that aren't in your main table yet http://msdn.microsoft.com/en-us/library/ms188055.aspx
So for example: