SQL 如何使用Sql Server 2008比较不同数据库中两个表之间的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7606175/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to compare data between two table in different databases using Sql Server 2008?
提问by Jagadis Sahu
I have two database's, named DB1 and DB2 in Sql server 2008. These two database's have the same tables and same table data also. However, I want to check if there are any differences between the data in these tables.
我有两个数据库，在 Sql server 2008 中命名为 DB1 和 DB2。这两个数据库的表和表数据也相同。但是，我想检查这些表中的数据之间是否存在差异。
Could anyone help me with a script for this?
任何人都可以帮我写一个脚本吗？
回答by Mikael Eriksson
select *
from (
select *
from DB1.dbo.Table
except
select *
from DB2.dbo.Table
) as T
union all
select *
from (
select *
from DB2.dbo.Table
except
select *
from DB1.dbo.Table
) as T
Test code:
测试代码：
declare @T1 table (ID int)
declare @T2 table (ID int)
insert into @T1 values(1),(2)
insert into @T2 values(2),(3)
select *
from (
select *
from @T1
except
select *
from @T2
) as T
union all
select *
from (
select *
from @T2
except
select *
from @T1
) as T
Result:
结果：
ID
-----------
1
3
回答by JdMR
I'd really suggest that people who encounter this problem go and find a third party database comparison tool.
我真的建议遇到这个问题的人去寻找第三方数据库比较工具。
Reason – these tools save a lot of timeand make the process less error prone.
原因 - 这些工具可以节省大量时间，并使流程不易出错。
I've used comparison toolsfrom ApexSQL (Diff and Data Diff) but you can't go wrong with other tools marc_s and Marina Nastenko already pointed out.
我使用过ApexSQL 的比较工具（Diff 和 Data Diff），但使用 marc_s 和 Marina Nastenko 已经指出的其他工具不会出错。
If you're absolutely sure that you are only going to compare tables once then SQL is fine but if you're going to need this from time to time you'll be better off with some 3rd party tool.
如果您绝对确定只比较一次表，那么 SQL 很好，但如果您不时需要它，那么使用某些 3rd 方工具会更好。
If you don't have budget to buy it then just use it in trial mode to get the job done.
如果您没有购买它的预算，那么只需在试用模式下使用它即可完成工作。
I hope new readers will find this useful even though it's a late answer…
我希望新读者会发现这很有用，即使这是一个迟到的答案......
回答by Matt
I'v done things like this using the Checksum(*) function
我已经使用 Checksum(*) 函数完成了这样的操作
In essance it creates a row level checksum on all the columns data, you could then compare the checksum of each row for each table to each other, use a left join, to find rows that are different.
本质上，它在所有列数据上创建一个行级校验和，然后您可以将每个表的每一行的校验和相互比较，使用左连接来查找不同的行。
Hope that made sense...
希望这是有道理的...
Better with an example....
最好举个例子....
select *
from
( select checksum(*) as chk, userid as k from UserAccounts) as t1
left join
( select checksum(*) as chk, userid as k from UserAccounts) as t2 on t1.k = t2.k
where t1.chk <> t2.chk
回答by edward
Comparing the two Databases in SQL Database. Try this Query it may help.
比较 SQL 数据库中的两个数据库。试试这个查询它可能会有所帮助。
SELECT T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS
system_data_type FROM [***Database Name 1***].sys.[tables] AS T
INNER JOIN [***Database Name 1***].sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN [***Database Name 1***].sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
EXCEPT SELECT T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS system_data_type FROM ***Database Name 2***.sys.[tables] AS T
INNER JOIN ***Database Name 2***.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN ***Database Name 2***.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
回答by evpo
select * from DB1.dbo.Table a inner join DB2.dbo.Table b on b.PrimKey = a.PrimKey
where a.FirstColumn <> b.FirstColumn ...
Checksum that Matt recommended is probably a better approach to compare columns rather than comparing each column
马特推荐的校验和可能是比较列而不是比较每一列的更好方法
回答by CharithJ
If the database are in the same server use [DatabaseName].[Owner].[TableName]format when accessing a table that resides in a different database.
如果数据库在同一服务器中，则[DatabaseName].[Owner].[TableName]在访问位于不同数据库中的表时使用格式。
Eg: [DB1].[dbo].[TableName]
例如： [DB1].[dbo].[TableName]
If databases in different server look at on Creating Linked Servers (SQL Server Database Engine)
如果不同服务器中的数据库在创建链接服务器（SQL Server 数据库引擎）上查看
回答by Bogdan Sahlean
Another solution (non T-SQL): you can use tablediffutility.
For example if you want to compare two tables (Localitate) from two different servers (ROBUH01 & ROBUH02) you can use this shell command:
另一个解决方案（非T-SQL）：您可以使用tablediff实用程序。例如，如果要比较Localitate来自两个不同服务器（ROBUH01 和 ROBUH02）的两个表 ( )，可以使用以下 shell 命令：
C:\Program Files\Microsoft SQL Server0\COM>tablediff -sourceserver ROBUH01 -s
ourcedatabase SIM01 -sourceschema dbo -sourcetable Localitate -destinationserver
ROBUH02 -destinationschema dbo -destinationdatabase SIM02 -destinationtable Lo
calitate
Results:
结果：
Microsoft (R) SQL Server Replication Diff Tool Copyright (c) 2008 Microsoft Corporation User-specified agent parameter values:
-sourceserver ROBUH01
-sourcedatabase SIM01
-sourceschema dbo
-sourcetable Localitate
-destinationserver ROBUH02
-destinationschema dbo
-destinationdatabase SIM02
-destinationtable Localitate
Table [SIM01].[dbo].[Localitate] on ROBUH01 and Table [SIM02].[dbo].[Localitate ] on ROBUH02 have 10 differences.
Err Id Dest.
Only 21433 Dest.
Only 21434 Dest.
Only 21435 Dest.
Only 21436 Dest.
Only 21437 Dest.
Only 21438 Dest.
Only 21439 Dest.
Only 21441 Dest.
Only 21442 Dest.
Only 21443
The requested operation took 9,9472657 seconds.
------------------------------------------------------------------------
回答by user3209145
If both database on same server. You can check similar tables by using following query :
如果两个数据库在同一台服务器上。您可以使用以下查询检查类似的表：
select
fdb.name, sdb.name
from
FIRSTDBNAME.sys.tables fdb
join SECONDDBNAME.sys.tables sdb
on fdb.name = sdb.name -- compare same name tables
order by
1
By listing out similar table you can compare columns schema using sys.columnsview.
通过列出类似的表，您可以使用sys.columns视图比较列模式。
Hope this helps you.
希望这对你有帮助。
回答by MOULOU
In order to compare two databases, I've written the procedures bellow. If you want to compare two tables you can use procedure 'CompareTables'. Example :
为了比较两个数据库，我编写了以下程序。如果要比较两个表，可以使用过程“CompareTables”。例子 ：
EXEC master.dbo.CompareTables 'DB1', 'dbo', 'table1', 'DB2', 'dbo', 'table2'
If you want to compare two databases, use the procedure 'CompareDatabases'. Example :
如果要比较两个数据库，请使用过程“CompareDatabases”。例子 ：
EXEC master.dbo.CompareDatabases 'DB1', 'DB2'
Note : - I tried to make the procedures secure, but anyway, those procedures are only for testing and debugging. - If you want a complete solution for comparison use third party like (Visual Studio, ...)
注意： - 我试图使程序安全，但无论如何，这些程序仅用于测试和调试。- 如果您想要一个完整的比较解决方案，请使用第三方（Visual Studio，...）
USE [master]
GO
create proc [dbo].[CompareDatabases]
@FirstDatabaseName nvarchar(50),
@SecondDatabaseName nvarchar(50)
as
begin
-- Check that databases exist
if not exists(SELECT name FROM sys.databases WHERE name=@FirstDatabaseName)
return 0
if not exists(SELECT name FROM sys.databases WHERE name=@SecondDatabaseName)
return 0
declare @result table (TABLE_NAME nvarchar(256))
SET NOCOUNT ON
insert into @result EXEC('(Select distinct TABLE_NAME from ' + @FirstDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
+'Where TABLE_SCHEMA=''dbo'')'
+ 'intersect'
+ '(Select distinct TABLE_NAME from ' + @SecondDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
+'Where TABLE_SCHEMA=''dbo'')')
DECLARE @TABLE_NAME nvarchar(256)
DECLARE curseur CURSOR FOR
SELECT TABLE_NAME FROM @result
OPEN curseur
FETCH curseur INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
print 'TABLE : ' + @TABLE_NAME
EXEC master.dbo.CompareTables @FirstDatabaseName, 'dbo', @TABLE_NAME, @SecondDatabaseName, 'dbo', @TABLE_NAME
FETCH curseur INTO @TABLE_NAME
END
CLOSE curseur
DEALLOCATE curseur
SET NOCOUNT OFF
end
GO
.
.
USE [master]
GO
CREATE PROC [dbo].[CompareTables]
@FirstTABLE_CATALOG nvarchar(256),
@FirstTABLE_SCHEMA nvarchar(256),
@FirstTABLE_NAME nvarchar(256),
@SecondTABLE_CATALOG nvarchar(256),
@SecondTABLE_SCHEMA nvarchar(256),
@SecondTABLE_NAME nvarchar(256)
AS
BEGIN
-- Verify if first table exist
DECLARE @table1 nvarchar(256) = @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME
DECLARE @return_status int
EXEC @return_status = master.dbo.TableExist @FirstTABLE_CATALOG, @FirstTABLE_SCHEMA, @FirstTABLE_NAME
IF @return_status = 0
BEGIN
PRINT @table1 + ' : Table Not FOUND'
RETURN 0
END
-- Verify if second table exist
DECLARE @table2 nvarchar(256) = @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME
EXEC @return_status = master.dbo.TableExist @SecondTABLE_CATALOG, @SecondTABLE_SCHEMA, @SecondTABLE_NAME
IF @return_status = 0
BEGIN
PRINT @table2 + ' : Table Not FOUND'
RETURN 0
END
-- Compare the two tables
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = '('
+ '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
+ 'EXCEPT'
+ '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
+ ')'
+ 'UNION'
+ '('
+ '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
+ 'EXCEPT'
+ '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
+ ')'
DECLARE @wrapper AS NVARCHAR(MAX) = 'if exists (' + @sql + ')' + char(10) + ' (' + @sql + ')ORDER BY 2'
Exec(@wrapper)
END
GO
.
.
USE [master]
GO
CREATE PROC [dbo].[TableExist]
@TABLE_CATALOG nvarchar(256),
@TABLE_SCHEMA nvarchar(256),
@TABLE_NAME nvarchar(256)
AS
BEGIN
IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=@TABLE_CATALOG)
RETURN 0
declare @result table (TABLE_SCHEMA nvarchar(256), TABLE_NAME nvarchar(256))
SET NOCOUNT ON
insert into @result EXEC('Select TABLE_SCHEMA, TABLE_NAME from ' + @TABLE_CATALOG + '.INFORMATION_SCHEMA.COLUMNS')
SET NOCOUNT OFF
IF EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME FROM @result
WHERE TABLE_SCHEMA=@TABLE_SCHEMA AND TABLE_NAME=@TABLE_NAME)
RETURN 1
RETURN 0
END
GO
