Geek asks and answers

How to compare two structurally similar tables and only return the column name and the value of different values ​​in SQL Server?

I am using SQL Server 2008 (v10.0 SP3) as my database.

I am trying to find how to compare two almost identical table structures, and only return the column name and the value of columns that do not match.

I have two tables.

Table A contains about 260 columns and a unique identifier for each record. It comes from a view on another server.

Table B is a copy of table A in the structure with an insert date column and an action column.

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
    DROP TABLE #TableA
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
    DROP TABLE #TableB

CREATE TABLE #TableA (
UniqueID INT,[Name] CHAR(3),[Address] CHAR(15),
HairColor CHAR(6),ImportDate DATETIME
)

CREATE TABLE #TableB (
UniqueID INT,[Name] CHAR(3),[Address] CHAR(15),
HairColor CHAR(6),ImportDate DATETIME,
AuditDate DATETIME,[Action] CHAR(10)
)

INSERT INTO #TableA
VALUES (1,'Joe','1 Main St.','Brown','12/1/2013')

INSERT INTO #TableA
VALUES (2,'Jen','1 Main St.','Red','12/1/2013')

INSERT INTO #TableB
VALUES (2,'Jen','1 Main St.','Blonde','10/1/2013','12/1/2013','CHANGE')

INSERT INTO #TableB
VALUES (2,'Jen','1 Baker St.','Blonde','4/1/2010','10/1/2013','CHANGE')

INSERT INTO #TableB
VALUES (2,'Jen','4 Deer Ave.','Black','6/1/2004','4/1/2010','CHANGE')

SELECT * FROM #TableA AS ta

SELECT * FROM #TableB AS tb

Table a

╔══════════╦══════╦════════════╦═══════════╦════════════╗
β•‘ UniqueID β•‘ Name β•‘  Address   β•‘ HairColor β•‘ ImportDate β•‘
╠══════════╬══════╬════════════╬═══════════╬════════════╣
β•‘        1 β•‘ Joe  β•‘ 1 Main St. β•‘ Brown     β•‘ 12/1/2013  β•‘
β•‘        2 β•‘ Jen  β•‘ 1 Main St. β•‘ Red       β•‘ 12/1/2013  β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•

Table B

╔══════════╦══════╦═════════════╦═══════════╦════════════╦═══════════╦════════╗
β•‘ UniqueID β•‘ Name β•‘   Address   β•‘ HairColor β•‘ ImportDate β•‘ AuditDate β•‘ Action β•‘
╠══════════╬══════╬═════════════╬═══════════╬════════════╬═══════════╬════════╣
β•‘        2 β•‘ Jen  β•‘ 1 Main St.  β•‘ Blonde    β•‘ 10/1/2013  β•‘ 12/1/2013 β•‘ CHANGE β•‘
β•‘        2 β•‘ Jen  β•‘ 1 Baker St. β•‘ Blonde    β•‘ 4/1/2010   β•‘ 10/1/2013 β•‘ CHANGE β•‘
β•‘        2 β•‘ Jen  β•‘ 4 Deer Ave. β•‘ Black     β•‘ 6/1/2004   β•‘ 4/1/2010  β•‘ CHANGE β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•

A , , A ( A) , A ( A).

A , A, A B insertdate DELETED .

A , A, A B insertdate ADDED .

A , , AI, A B insertdate CHANGE .

, B , A, A .

. . 260 B, A, TableA.UniqueID = TableB.UniqueID

UniqueID A - :

╔══════════╦════════════╦═════════════╦════════════╦═══════════╦════════╗
β•‘ UniqueID β•‘ ColumnName β•‘    Value    β•‘ ImportDate β•‘ AuditDate β•‘ Action β•‘
╠══════════╬════════════╬═════════════╬════════════╬═══════════╬════════╣
β•‘        2 β•‘ HairColor  β•‘ Blonde      β•‘ 10/1/2013  β•‘ 12/1/2013 β•‘ CHANGE β•‘
β•‘        2 β•‘ Address    β•‘ 1 Baker St. β•‘ 4/1/2010   β•‘ 10/1/2013 β•‘ CHANGE β•‘
β•‘        2 β•‘ HairColor  β•‘ Blonde      β•‘ 4/1/2010   β•‘ 10/1/2013 β•‘ CHANGE β•‘
β•‘        2 β•‘ Address    β•‘ 4 Deer Ave. β•‘ 6/1/2004   β•‘ 4/1/2010  β•‘ CHANGE β•‘
β•‘        2 β•‘ HairColor  β•‘ Black       β•‘ 6/1/2004   β•‘ 4/1/2010  β•‘ CHANGE β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•

, .

. ORDINAL_POSITION INFORMATION_SCHEMA.COLUMNS ?

SELECT a.ORDINAL_POSITION,a.COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS AS a 
JOIN INFORMATION_SCHEMA.COLUMNS AS b ON a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.TABLE_NAME = 'TableA' AND a.TABLE_SCHEMA='dbo'
AND b.TABLE_NAME = 'TableB' AND b.TABLE_SCHEMA='dbo'
+3
3

, UNPIVOT - , . MSDN: PIVOT UNPIVOT

:

SELECT UniqueID, ColumnName, Value, ImportDate, AuditDate, Action
FROM
(
    SELECT 
        TableA.UniqueID, 
        CAST((CASE WHEN ISNULL(TableB.Address, '') <> ISNULL(TableA.Address, '')
              THEN ISNULL(TableB.Address, '')
              ELSE NULL END) AS nvarchar(255))
        AS Address, 
        CAST((CASE WHEN ISNULL(TableB.HairColor, '') <> ISNULL(TableA.HairColor, '')
              THEN ISNULL(TableB.HairColor, '')
              ELSE NULL END) AS nvarchar(255))
        AS HairColor, 
        TableB.ImportDate,
        TableB.AuditDate,
        TableB.Action
    FROM TableA INNER JOIN TableB ON TableB.UniqueID = TableA.UniqueID
) AS p
UNPIVOT
(
    Value FOR ColumnName in (Address, HairColor)
) AS up
WHERE Value IS NOT NULL

, nvarchar(255) . , - .

, , , , . . , INNER JOIN, LEFT JOIN . , - .

, , , , .

0

...

SELECT a.UniqueID,'HairColour' [ColumnName],b.HairColour [Value],...,'CHANGE' [Action] FROM Tableb b
INNER JOIN Tablea a ON
   a.UniqueID = b.uniqueID
WHERE a.HairColor <> b.HairColor

UNION

    SELECT a.UniqueID,'Address' [ColumnName],b.Address [Value],...,'CHANGE' [Action] FROM Tableb b
INNER JOIN Tablea a ON
   a.UniqueID = b.uniqueID
WHERE a.Address <> b.Address

etc...
0

If you have Visual Studio Premium or Ultimate, you can use the schema comparison database project for this purpose. This is quite complicated and even allows you to generate DDL for differences in source and / or purpose. You can also compare data if necessary. I have used it in many projects in the past.

MSDN: Compare and synchronize database schemas .

0
source

More articles:


All Articles