比较两个不同的数据库 [英] Comparison of two different databases

问题描述

我使用SQL 2000在同一台服务器上有两个不同的数据库。每晚数据库1都是通过自动化过程更新的。我目前有一个查询，然后将该数据移动到静态数据。我可以操纵但我需要的表格。我正在尝试构建的是触发器或SP，它将允许我检查新上传的数据和静态数据之间的差异。数据，然后将任何新内容移动到名为newclients的表中。我已经在线查看了很多解决方案，但大多数解决方案似乎涉及一个漫长的复杂过程。有一个相对简单易行的方法来写这个。目前，这就是我所拥有的：

I have two different databases on the same server using SQL 2000. Every night database 1 is updated via an automated process. I currently have an query that will then move that data to a "static" table that I can manipulate however I need. What I''m trying to build is either a trigger or a SP that will allow me to check the differences between the newly uploaded data and the "static" data and then move whatever is new to a table called "newclients." I''ve looked at a lot of solutions on line but most seem to involve a long convoluted process to do this. Is there a relatively simple and easy way to write this. Currently, this is what I have:

展开 | 选择 | Wrap | 行号

推荐答案

Doug，

你能改写一下吗？很难理解你想说的话。我建议你使用对象名称然后它会更清楚你指的是什么。

Doug,

Could you rewrite this do you think. It''s hard to follow what you''re trying to say. I suggest you use object names and then it will be clearer what you''re referring to.

Neo，


好​​的我会的尝试尽可能简单地写这个。


我有2个数据库。数据库A每晚由一个在午夜产生数据的应用程序填充。因此，每天午夜，数据库A中的数据都会被覆盖。数据库B是每晚被覆盖之前的数据库a的副本。我想要做的是在每天晚上数据库A中的数据被覆盖之前比较A和B，看看两者之间有什么不同，然后将已经编辑或添加的内容添加到表新中。 ;那更有意义吗？

Neo,

Ok I''ll try to write this as simply as I can.

I have 2 databases. Database A is filled every night by an application that produces the data at midnight. So every night at midnight the data in database A is overwritten. Database B is a copy of database a before it''s overwritten every night. What I''d like to do is before the data in database A is overwritten every night is to compare A and B, see whats different between the two and then whatever has been edited or added is then added to table "New." Does that make more sense?

是的，它确实谢谢你。名字不多，但它更清晰，所以你可以逃避;）现在我们可以调用数据库A TableA中的表和数据库B TableB中的表。记住当然TableB保存原始数据，而TableA保存更新的数据。


基本上我想你想要一张Deltas表，或者有效的更改，已经对表做了在此过程中。如果我有这个错误，请告诉我，但这意味着它应该包括以下所有内容：

Yes it does thank you. Not many names, but it is clearer so you get away with that ;) For now we can call the table in database A TableA and the table from database B TableB. Remembering of course that TableB holds the original data while TableA holds the updated data.

Essentially then I guess you want a table of Deltas, or effective changes, that have been made to the table during the process. Let me know if I have this wrong, but that would mean it should include all of :

1. 添加。足够简单的数据后图像。
2. 对现有记录的修正。这可以像之前和之前那样完成。 After-Images，或者对于要求较低的情况，只需要After-Image即可。
3. 删除。这些可能不是必需的，但如果您需要能够重现更改，则可能会出现这些情况。

假设同时访问这两个表，您将创建一个查询，用于追加（进入[New]）来自TableA的所有记录，其中TableB的匹配记录（所有字段上的LEFT OUTER JOIN）都不存在。如果修改的After-Image需要不同的标志而不是添加的标志，则加入唯一索引并比较WHERE子句中的其他数据。


很好地，可以反过来使用相同的技术来找到修改的删除和前映像。两个相对简单的查询可以为您完成整个工作。


PS。这不是简单的解释，而是清晰度。您的原始帖子相对简单，但不清楚。

Assuming access to both tables simultaneously then, you would create a query to append (into [New]) all records from TableA where the matching record from TableB (LEFT OUTER JOIN on all fields) doesn''t exist. If a different flag is required for an After-Image of an amendment as opposed to that for an addition, then join on a unique index and compare the other data in the WHERE clause.

Pretty well the same technique can be used in reverse to find deletions and Before-Images of amendments. Two relatively straightforward queries can do the whole job for you.

PS. It''s not about simplicity of the explanation, but rather about the clarity. Your original post was relatively simple, but not clear.

这篇关于比较两个不同的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！