Implement symmetric difference in SQL Server?

Question

Here's a problem I've been trying to solve at work. I'm not a database expert, so that perhaps this is a bit sophomoric. All apologies.

I have a given database D, which has been duplicated on another machine (in a perhaps dubious manner), resulting in database D'. It is my task to check that database D and D' are in fact exactly identical.

The problem, of course, is what to actually do if they are not. For this purpose, my thought was to run a symmetric difference on each corresponding table and see the differences.

There is a "large" number of tables, so I do not wish to run each symmetric difference by hand. How do I then implement a symmetric difference "function" (or stored procedure, or whatever you'd like) that can run on arbitrary tables without having to explicitly enumerate the columns?

This is running on Windows, and your hedge fund will explode if you don't follow through. Good luck.

Answer 1

You can achieve this by doing something like this.

I have used a function to split comma separated value into a table to demostrate.

CREATE FUNCTION [dbo].[Split]
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END
GO


DECLARE @WB_LIST varchar(1024) = '123,125,764,256,157';
DECLARE @WB_LIST_IN_DB varchar(1024) = '123,125,795,256,157,789';

DECLARE @TABLE_UPDATE_LIST_IN_DB TABLE ( id varchar(20));
DECLARE @TABLE_UPDATE_LIST TABLE ( id varchar(20));

INSERT INTO @TABLE_UPDATE_LIST
SELECT data FROM dbo.Split(@WB_LIST,',');

INSERT INTO @TABLE_UPDATE_LIST_IN_DB
SELECT data FROM dbo.Split(@LIST_IN_DB,',');


SELECT * FROM @TABLE_UPDATE_LIST
EXCEPT
SELECT * FROM @TABLE_UPDATE_LIST_IN_DB
UNION
SELECT * FROM @TABLE_UPDATE_LIST_IN_DB
EXCEPT
SELECT * FROM @TABLE_UPDATE_LIST;

Answer 2

Here is the solution. The example data is from the ReportServer database that comes with SSRS 2008 R2, but you can use it on any dataset:

SELECT s.name, s.type 
FROM 
(
    SELECT s1.name, s1.type
    FROM syscolumns s1
    WHERE object_name(s1.id) = 'executionlog2'
    UNION ALL 
    SELECT s2.name, s2.type
    FROM syscolumns s2 
    WHERE object_name(s2.id) = 'executionlog3'
) AS s 
GROUP BY s.name, s.type   
HAVING COUNT(s.name) = 1

Answer 3

SQL Server 2000 Added the "EXCEPT" keyword, which is almost exactly the same as Oracle's "minus"

SELECT * FROM TBL_A WHERE ...
EXCEPT
SELECT * FROM TBL_B WHERE ...

Answer 4

My first reaction is to suggest duplicating to the other machine again in a non-dubious manner.

If that is not an option, perhaps some of the tools available from Red Gate could do what you need.

(I am in no way affliated with Red Gate, just remember Joel mentioning how good their tools were on the podcast.)

Answer 5

Use the SQL Compare tools by Red Gate. It compares scheamas, and the SQL Data Compare tool compares data. I think that you can get a free trial for them, but you might as well buy them if this is a recurring problem. There may be open source or free tools like this, but you might as well just get this one.