问题描述
我有同一个数据库的两个实例.第一个 db 代表今天的数据,第二个数据来自 6 个月前.我需要找到特定表中条目子集的差异.
I have two instances of the same database. The first db represents data from today, the second data from 6 months ago. I need to find differences for a subset of entries in a specific table.
对于两个表中都有 id 的条目,我想找到一种方法来仅查看不相同的行.
For entries with ids that are in both tables, I'd like to find a way to view only the rows that aren't identical.
有什么想法吗?
谢谢
推荐答案
SELECT t1.id FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id WHERE ISNULL(t1.field1,'') <> ISNULL(t2.field1,'') OR ISNULL(t1.field2,'') <> ISNULL(t2.field2,'') OR ...
要生成长的 WHERE 部分,您可以使用此功能:
To produce long WHERE part you can use this function:
CREATE PROCEDURE compareTables @db1 NVARCHAR(100), @table1 NVARCHAR(100), @db2 NVARCHAR(100), @table2 NVARCHAR(100) AS BEGIN SET NOCOUNT ON; DECLARE @where NVARCHAR(MAX) DECLARE @cmd NVARCHAR(MAX) SET @where = '' SELECT @where = @where + 'ISNULL(t1.' + name + ','''') <> ISNULL(t2.' + name + ','''') OR ' FROM sys.columns WHERE object_id = OBJECT_ID(@table1) SET @where = SUBSTRING(@where,1,LEN(@where)-3) SET @cmd = 'SELECT t1.id FROM ' + @db1 + '.' + @table1 + ' t1 ' SET @cmd = @cmd + 'INNER JOIN ' + @db2 + '.' + @table2 + ' t2 ON t1.id = t2.id ' SET @cmd = @cmd + 'WHERE ' + @where EXEC sp_executesql @cmd END GO
示例用法:
EXEC compareTables 'db1_name','dbo.table1','db2_name','dbo.table1'
记得把schema放在表名中.
Remember to put schema in the table name.