問題描述

我有同一個(gè)數(shù)據(jù)庫的兩個(gè)實(shí)例.第一個(gè) db 代表今天的數(shù)據(jù)，第二個(gè)數(shù)據(jù)來自 6 個(gè)月前.我需要找到特定表中條目子集的差異.

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.

對(duì)于兩個(gè)表中都有 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.

這篇關(guān)于比較兩個(gè) T-SQL 表的差異的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！