SQL Server 2000 中的表差异

发布于 2024-08-05 16:39:28 字数 268 浏览 5 评论 0原文

我正在使用 SQL Server 2000，并且给定一组数据（具有唯一 ID），我想找出数据库中行的差异，并匹配唯一 ID。我也在努力保持尽可能的灵活性。

我可以在前端使用C#/VB来获取参数，甚至返回东西。也许传入 XML 并获取 XML 作为回报？

例如，我想要一个函数调用：

// returns the columns that are different
func( A, B, C ) {

}

有什么想法吗？

原文

I'm using SQL Server 2000, and given a a set of data (with unique ID's), I want to figure out the diffs with rows in the database, matching on the unique IDs. I'm also trying to keep it as flexible as possible.

I can use C#/VB in the front end to take the parameters, or even return things. Maybe passing in XML and getting XML in return?

For example, I want a function that calls:

// returns the columns that are different
func( A, B, C ) {

}

Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮萍、无处依 2024-08-12 16:39:28

您可以在 UNION 中使用一个很酷的技巧：

SELECT MAX(tbl) AS TABLE_NAME, unique_key
FROM (
    SELECT 'table1' AS tbl, unique_key
    FROM table1
    UNION ALL
    SELECT 'table2' AS tbl, unique_key
    FROM table2
) AS X GROUP BY unique_key
HAVING COUNT(*) = 1

这将显示一侧或另一侧具有另一侧没有的行。

显然，这可以扩展到做更多事情。

或者，您可以执行INNER JOIN（匹配数据不同的键）、LEFT JOIN（一侧缺少键）和RIGHT JOIN （另一个键缺失）并将它们全部结合在一起。

我实际上有一个实用程序 SP（它根据您需要的选项使用一种或另一种方法），它将比较任何两个表，并具有用于设置哪些列被视为键的一部分、要忽略哪些列、限制每一侧的选项它甚至可以选择将差异写入表中。

There's a cool trick you can use with UNION:

SELECT MAX(tbl) AS TABLE_NAME, unique_key
FROM (
    SELECT 'table1' AS tbl, unique_key
    FROM table1
    UNION ALL
    SELECT 'table2' AS tbl, unique_key
    FROM table2
) AS X GROUP BY unique_key
HAVING COUNT(*) = 1

This will show where one side or the other has rows which the other doesn't have.

This can be expanded to do more, obviously.

Alternatively, you can do an INNER JOIN (matches on keys where data is different), LEFT JOIN (key missing on one side) and RIGHT JOIN (key missing on the other) and UNION them all together.

I actually have a utility SP (it uses one or the other method depending on what options you need) which will compare any two tables and has options for setting which columns are considered the part of keys, which columns to ignore, restrict each side to a subset, etc. It even has an option to write the differences to a table.

回复收藏 0 原文