辨識與主行不匹配的行

我正在將來自不同伺服器上不同數據庫的一堆表與主記錄進行比較。我需要知道由 標識的哪些伺服器locationID具有不匹配的行，因為它們可能需要維護。

我有一個簡單的EXCEPT查詢，我比較了一個表，其中每一行都是來自每個伺服器的配置；table1每台伺服器有一行，所有配置加上locationID這是一個告訴我它是哪台伺服器的列。我將所有這些與table1_master具有正確設置的表進行比較，但我排除了它，locationID因為它不匹配。

下面的簡單查詢：

SELECT everything, but, locationID
FROM table1
EXCEPT
SELECT everything, but, locationID
FROM table1_master

我比較所有伺服器時只有一個主行，我沒有locationID在這裡選擇它。

這是我正在比較的行的一個範例。每個都有一個主鍵、一個列varchar和一個包含數十個列的巨大列表。我想比較除LocationID 之外的所有列，但我需要 LocationID 來辨識行。

LocationID             setting    setting    setting     setting
CS02      C            Y           Y         Y           Y
CS03      C            Y           Y         Y           Y
CS06      C            Y           N         Y           Y

在此範例中，CS02 是我的主記錄，因此由於 CS02 和 CS03 中的所有設置都相同，因此這些行不會顯示，但 CS06 會顯示。但在我的EXCEPT查詢中，我實際上並沒有擷取 LocationID，所以我實際上並不知道返回了哪一行。

這會返回我需要的行，但不是locationID，所以我不知道哪些行是錯誤的。有什麼方法可以locationID在踢出匹配行的同時包含在結果集中？

我想到的解決方案是為table1_master表中的每個伺服器創建一行，因此每個都locationID被表示，但除此之外它們都具有相同的數據。然後我的EXCLUDE查詢應該返回locationID和我的資訊，但這是最好的方法嗎？

您也可以使用動態 SQL 來執行此操作，而無需手動建構所有列名。

DECLARE @sql NVARCHAR(MAX), @c1 NVARCHAR(MAX), @c2 NVARCHAR(MAX);

SELECT @c1 = N'', @c2 = N'';

SELECT 
 @c1 = @c1 + ',' + QUOTENAME(name),
 @c2 = @c2 + ' AND m.' + QUOTENAME(name) + ' = s.' + QUOTENAME(name)
FROM sys.columns
WHERE name <> 'LocationID'
AND [object_id] = OBJECT_ID('dbo.table1');

SET @sql = ';WITH s AS (
      SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1
      EXCEPT 
      SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1_master
    ) 
    SELECT m.LocationID
FROM s INNER JOIN dbo.table1 AS m ON 1 = 1
' + @c2;

SELECT @sql;
--EXEC sp_executesql @sql;

您可以按原樣獲取此查詢的輸出並將查詢儲存在某處，或者您可以註釋掉SELECT並取消註釋EXEC並將其保留為永久動態 SQL - 在這種情況下，它將自動適應兩個表中的列更改。

另一個想法（假設 LocationID 是唯一的） - 我突然想到您可能想要包含主行，以便您可以快速發現不同的列：

 ;WITH c AS 
 (
   SELECT t.LocationID, m.setting1, m.setting2, ...
     FROM dbo.table1 AS t CROSS JOIN dbo.table1_master AS m
 )
 SELECT DISTINCT src = '> master', setting1, setting2, ...
   FROM c
 UNION ALL
 (
   SELECT RTRIM(LocationID), setting1, setting2, ...
     FROM dbo.table1
   EXCEPT
   SELECT RTRIM(LocationID), setting1, setting2, ...
     FROM c
 )
 ORDER BY src;

這個版本稍微便宜一些（主要是通過避免DISTINCT對主表的影響，代價是需要再次指定所有列 - 您可以再次按照上面的方式自動化）：

 ;WITH m AS 
 (
   SELECT setting1, setting2, ... 
     FROM dbo.table1_master
 ),
 c AS 
 (
   SELECT src = RTRIM(t.LocationID), m.setting1, m.setting2, ...
     FROM dbo.table1 AS t CROSS JOIN m
 )
 SELECT src = '> master', setting1, setting2, ...
   FROM m
 UNION ALL
 (
   SELECT RTRIM(LocationID), setting1, setting2, ...
     FROM dbo.table1
   EXCEPT
   SELECT src, setting1, setting2, ...
     FROM c
 )
 ORDER BY src;

然而，所有這些選項的性能都比 Rachel 的 simple 更差，而且計劃更差LEFT JOIN。我試圖堅持使用的主題，EXCEPT即使它更多地是關於語法而不是性能。

關鍵的一點是，如果列數太多而無法手動處理，您可以使用上面的動態 SQL 方法來建構您想要使用的任何查詢——您可以這樣做一次並儲存結果，或者擁有程式碼每次生成。要使用動態 SQL 生成 Rachel 的查詢，不需要太多更改：

DECLARE @sql NVARCHAR(MAX), @and NVARCHAR(MAX), @anycol NVARCHAR(128);
SELECT @sql = N'', @and = N'';

SELECT @and = @and + ' AND t.' + QUOTENAME(name) + ' = m.' + QUOTENAME(name)
 FROM sys.columns
 WHERE [object_id] = OBJECT_ID('dbo.table1_master');

SELECT TOP (1) @anycol = QUOTENAME(name)
 FROM sys.columns
 WHERE [object_id] = OBJECT_ID('dbo.table1_master')
 ORDER BY name;

SET @sql = 'SELECT locationID
FROM dbo.table1 AS t
LEFT OUTER JOIN dbo.table1_master AS m ON 1 = 1' 
 + @and + ' WHERE m.' + @anycol + ' IS NULL;';

SELECT @sql;
--EXEC sp_executesql @sql;

引用自：https://dba.stackexchange.com/questions/16711