T-SQL - 是否有（免费）方法来比较两个表中的数据？

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

我有桌子和桌子b。（SQL Server 2008）

Both tables have the exact same schema.

两个表都具有完全相同的模式。

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

出于此问题的目的，请考虑表a =我的本地开发表，表b =实时表。

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.

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

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

任何可以做到这一点的免费工具，或者更好的方式我自己可以做到这一点？

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.

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

Anyone have any ideas?

有人有主意吗？

EDIT

编辑

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.

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

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

这里有一个关于TABLE A可能是什么样子的想法：

IdFoo          Activity      IsFoo
1              Foo           1
2              Bar           0

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

这里有一个关于TABLE B可能是什么样子的想法：

IdFoo          Activity      IsFoo
1              Foo           1
2              Bar           1

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.

基本上，我想要做的就是更新表B中的BIT列（IsFoo）以匹配表A中相应IdFoo的相应值。

Keep in mind:

记住：

TABLE A is on my local machine
表A在我的本地计算机上
TABLE B is on the live server
表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.

显然我在我的本地机器上有一个（可靠的）表B备份，我需要编写脚本，然后在实时服务器上运行脚本。

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.

该表还具有参照完整性（我未显示的其他列）。这意味着我不能只删除表B中的所有内容并从表A中执行插入操作。

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

这个脚本将是一次性的。所以不需要做像链接服务器，复制等的事情。通过伙伴们欣赏答案。 =）

EDIT:

编辑：

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

好的 - 所以我选择了Oleg的答案（VS2010 Data Compare）。快速，简单，有魅力。

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

不是说其他答案是不正确的。我很感激所有答案！

12 个解决方案

#1

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.

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

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.

在实际且更复杂的情况下，您应该考虑目标数据库中存在的参照完整性，不同的外键和身份（自动增量）字段，因此更新脚本将更加复杂。因此，我建议您不要花时间创建开发人员和生产数据库的同步，而应使用标准工具。我使用Visual Studio Team Edition 2008（或数据库版）或Visual Studio 2010 Ultimate版的功能来比较两个数据库中的数据。它工作得很好。

#2

Late answer but can be useful to the thread visitors

迟到的答案但对线程访问者有用

Besides other mentioned solutions, I can suggest trying ApexSQL Data Diff. It can compare and sync SQL Server database data (in live databases as well as backups), and automate and schedule data migrations. It works with huge databases also, and is able to perform comparisons directly from SSMS.

除了其他提到的解决方案，我可以建议尝试ApexSQL Data Diff。它可以比较和同步SQL Server数据库数据（在实时数据库和备份中），并自动化和计划数据迁移。它也适用于大型数据库，并且能够直接从SSMS执行比较。

You can download this tool for free and play with it. It features a fully functional free trial, and offers a community edition (after the trial period is over) that works on SQL Express and Azure SQL Database.

您可以免费下载此工具并使用它。它具有功能齐全的免费试用版，并提供适用于SQL Express和Azure SQL数据库的社区版（试用期结束后）。

To learn more about the tool, you may visit http://www.apexsql.com/sql_tools_datadiff.aspx

要了解有关该工具的更多信息，请访问http://www.apexsql.com/sql_tools_datadiff.aspx

#3

If you just want to synchronise the tables and don't care about reviewing the changes in advance the MERGE command can do this.

如果您只想同步表并且不关心提前查看更改，则MERGE命令可以执行此操作。

MSDN - MERGE (Transact-SQL)

MSDN - MERGE（Transact-SQL）

The (free) Microsoft SSDT also has data compare and synchronization built in though it is more limited than paid tools such as Redgate data compare.

（免费）Microsoft SSDT还内置了数据比较和同步功能，但它比Redgate数据比较等付费工具更受限制。

#4

There is SQL Data Compare from RedGate (though not free) and there is also SMO and the built in functionallity.

RedGate有SQL Data Compare（虽然不是免费的），还有SMO和内置的功能。

Finally Wikipedia has quite a comprehensive list of software.

最后，*拥有相当全面的软件列表。

#5

Since it is one off, you can use this query to find rows that are different in this two tables:

由于它是一次性的，您可以使用此查询来查找这两个表中不同的行：

(SELECT * FROM TABLE_A
 MINUS
 SELECT * FROM TABLE_B)

 UNION ALL

(SELECT * FROM TABLE_B
 MINUS
 SELECT * FROM TABLE_A)

MINUS will compare records field by field, then it will discard records from first table for which there is identical record in second table. This works like this:

MINUS将逐字段比较记录，然后它将丢弃第一个表中的记录，第二个表中的记录相同。这样工作如下：

First MINUS gets all records from TABLE_A that are not in TABLE_B
第一个MINUS从TABLE_B中获取不在TABLE_B中的所有记录
Second MINUS gets all records from TABLE_B that are not in TABLE_A
第二个MINUS获取TABLE_B中不在TABLE_A中的所有记录
Union gets all records from both tables for which there is no matching record in other table.
Union从两个表中获取所有记录，其他表中没有匹配的记录。

Now you can insert those records in some temp table and then do inserts/updates.

现在，您可以将这些记录插入某个临时表中，然后执行插入/更新。

Depending on your needs, you can restrict field list for comparing.

根据您的需要，您可以限制字段列表以进行比较。

Note that you need primary key for this to work.

请注意，您需要主键才能使用此功能。

Edit:
Ooops. SQL Server does not support MINUS operator. I'm working with ORACLE last year and half, so this was automatic.

编辑：哎呀。 SQL Server不支持MINUS运算符。去年我和ORACLE合作了一半，所以这是自动的。

You can use EXCEPT operator instead. See this article: EXCEPT and INTERSECT (Transact-SQL)

您可以使用EXCEPT运算符。查看本文：EXCEPT和INTERSECT（Transact-SQL）

Edit 2:

编辑2：

Re scherand's comment:
If he really cannot connect from local machine to live server, than he can simply dump TABLE_A and load it on server. One way or the other, goal is to change table data on live server.

Re scherand的评论：如果他真的无法从本地机器连接到实时服务器，那么他可以简单地转储TABLE_A并将其加载到服务器上。无论如何，目标是更改实时服务器上的表数据。

#6

I ran in the same problem as you - searched for a free tool that compares data from two MS SQL tables - and found nothing. Then I created a simple freeware command line utility. It compares data from two tables, and creates INSERT/DELETE/UPDATE statemenets to make destination table data the same as source. Now I am using it for data comparison, and as it is completely free, can recommend to check it out at: Sourceforge.net - UltraDBC

我遇到了和你一样的问题 - 搜索了一个比较来自两个MS SQL表的数据的免费工具 - 什么都没发现。然后我创建了一个简单的免费命令行实用程序。它比较来自两个表的数据，并创建INSERT / DELETE / UPDATE状态表以使目标表数据与源相同。现在我用它进行数据比较，因为它是完全免费的，可以建议查看：Sourceforge.net - UltraDBC

#7

You could use a data script generator that creates script for the inserts, and then use a file comparison tools such as WinMerge to compare the files to find the differences. There's an article on generating the data scripts on code project: http://www.codeproject.com/KB/database/sqlinsertupdategenerator.aspx

您可以使用为插入创建脚本的数据脚本生成器，然后使用文件比较工具（如WinMerge）来比较文件以查找差异。有一篇关于在代码项目上生成数据脚本的文章：http：//www.codeproject.com/KB/database/sqlinsertupdategenerator.aspx

#8

Tablediff would work - it's free and comes with SQL Server http://msdn.microsoft.com/en-us/library/ms162843.aspx

Tablediff可以工作 - 它是免费的，并附带SQL Server http://msdn.microsoft.com/en-us/library/ms162843.aspx

#9

You can also try using the import and export data provided by SQL Server 2008. Its is fairly straight way to copy all the data from anywhere to anywhere. I do the same thing and works perfectly.

您还可以尝试使用SQL Server 2008提供的导入和导出数据。它是将所有数据从任何地方复制到任何地方的相当直接的方法。我做同样的事情并且完美地工作。

#10

You might try our Schema Compare for SQL Server

您可以尝试我们的SQL Server架构比较

This tool is not free (it's shareware), but you can use 30-days trial for free and also, you have an opportunity to get a free license for this product - please refer to our free license conditions page.

此工具不是免费的（它是共享软件），但您可以免费使用30天试用版，此外，您还有机会获得此产品的免费许可 - 请参阅我们的免费许可条件页面。

#11

what happens if you:

如果您：

copy devTableA onto prod,
将devTableA复制到prod上，
suspend dependencies on prodTableB
暂停对prodTableB的依赖
rename prodTable B to prodTableB1,
将prodTable B重命名为prodTableB1，
rename devTableA to prodTableB
将devTableA重命名为prodTableB
reimplement dependencies on new prodTableB
重新实现对新prodTableB的依赖
test new prodTableB and then delete out-named old prodTableB
测试新的prodTableB然后删除out-named old prodTableB

？

or if you have to be more respectful of the existing prodTableB and your identity column values in both dev and prod match (even if the values are not continuous)...

或者如果您必须更加尊重现有的prodTableB以及dev和prod匹配中的标识列值（即使值不连续）......

declare @iLast int, @x int, @y int
select @iLast = (select MAX(id) from prodTableB)
set @x = 1
while @x <= @iLast
begin
  select @y = (select COUNT(*) from prodTableB where id = @x)
  if @y = 1
    begin
      update prodTableB set isFoo = (select isFoo from devTableA where id=@x
    end
  @x=@x+1
end

#12

You can also have a look at xSQL Data Compare. The SQL Express version is free and there is also a Lite version that will do the trick for small databases.

您还可以查看xSQL Data Compare。 SQL Express版本是免费的，还有一个Lite版本可以为小型数据库提供技巧。

A good free tool is also data compare from SSDT.

一个好的免费工具也是来自SSDT的数据比较。

Disclaimer: I'm affiliated with xSQL.

免责声明：我隶属于xSQL。

#1

#2