T-SQL - 是否有(免费)方法来比较两个表中的数据?

2021-09-10 00:00:00 tsql sql-server-2008 sql-server sqlcompare

我有table a 和table b.(SQL Server 2008)

I have table a and table b. (SQL Server 2008)

两个表具有完全相同的架构.

Both tables have the exact same schema.

对于这个问题，考虑 table a = 我的本地开发表，table b = 活动表.

For the purposes of this question, consider table a = my local dev table, table b = the live table.

我需要创建一个 SQL 脚本(包含 UPDATE/DELETE/INSERT 语句)，将表 b 更新为与表 a 相同.然后，此脚本将部署到实时数据库中.

I need to create a SQL script (containing UPDATE/DELETE/INSERT statements) that will update table b to be the same as table a. This script will then be deployed to the live database.

有没有可以做到这一点的免费工具，或者更好的方法是我自己可以做到这一点?

Any free tools out there that can do this, or better yet a way I can do it myself?

我想我可能需要对表中的所有字段进行某种类型的连接，然后基于它生成动态 SQL.

I'm thinking I probably need to do some type of a join on all the fields in the tables, then generate dynamic SQL based on that.

有人有什么想法吗?

编辑

好吧，我想我会澄清一下这个问题.

Okay, thought I'd clarify this question a little.

我需要同步的表是一个简单的查找表.数据非常简单明了.

The table I need to synchronize is a simple look-up table. The data is very simple and straightforward.

以下是 TABLE A 可能的样子:

Here's an idea of what TABLE A might look like:

IdFoo          Activity      IsFoo
1              Foo           1
2              Bar           0

以下是 TABLE B 可能的样子:

Here's an idea of what TABLE B might look like:

IdFoo          Activity      IsFoo
1              Foo           1
2              Bar           1

基本上，我想要做的就是更新 TABLE B 中的 BIT 列 (IsFoo) 以匹配 中的相应值>TABLE A 用于相同的 IdFoo.

Basically, all I want to do is update that BIT column (IsFoo) in TABLE B to match the corresponding value in TABLE A for the same IdFoo.

记住:

  • TABLE A 在我的本地机器上
  • TABLE B 在实时服务器上
  • TABLE A is on my local machine
  • TABLE B is on the live server

显然，我在本地机器上有一个 TABLE B 的(可靠的)备份，我需要针对它编写脚本，然后在实时服务器上运行脚本.

Obviously I have a (reliable) backup of TABLE B on my local machine which i need to script against, then run the script on the live server.

该表也具有参照完整性(我没有显示的其他列).这意味着我不能只是删除 TABLE B 中的所有内容并从 TABLE A 插入.

The table also has referential integrity (other columns I didn't show). Which means I can't just delete everything in TABLE B and do an insert from TABLE A.

这个脚本将是一次性的.因此，无需执行链接服务器、复制等操作.尽管各位大侠感谢答案.=)

This script will be a once off. So no need to do things like linked server, replication, etc. Appreciate the answers though guys. =)

好的 - 所以我接受了 Oleg 的回答(VS2010 数据比较).快速、简单，而且很有魅力.

Ok - so I've gone with Oleg's answer (VS2010 Data Compare). Quick, easy, and works a charm.

并不是说其他​​答案不正确.我感谢所有的答案！

Not to say the other answers are incorrect. I appreciate all the answers!

推荐答案

在非常简单的情况下，您将能够在本地 SQL Server 中定义一个新的链接服务器(请参阅 Microsoft SQL Server Management Studio 中的服务器对象"/链接服务器")并使用 INNER JOIN 和 OUTER JOIN 找出 A 表和 B 表之间的差异.

In very simple cases you will be able to define in your local SQL Server a new Linked Server (see in Microsoft SQL Server Management Studio under "Server Objects" / "Linked Server") and use INNER JOIN and OUTER JOIN to find out differences between A and B tables.

在真实且更复杂的情况下，您应该考虑目标数据库中存在的引用完整性、不同的外键和身份(自动增量)字段，因此更新脚本会更复杂.因此，我建议您不要花时间创建开发人员和生产数据库的同步，而是使用标准工具.例如，我使用 Visual Studio Team Edition 2008(或数据库版)或 Visual Studio 2010 Ultimate 版的功能来比较两个数据库中的数据.效果很好.

In a real and more complex situation you should take into consideration Referential Integrity, different Foreign Keys, and Identity (auto incremental) fields existing in the destination database, so the update script will be more complex. So I'll recommend you don't spend your time in creating synchronization of your developer and production DB and use a standard tool instead. I use for example the features of Visual Studio Team Edition 2008 (or Database edition) or Visual Studio 2010 Ultimate edition to compare data in two databases. It works very well.

相关文章