Fog Creek Software
Discussion Board




Table data comparison script on SQL Server 2k

Hi All,

I would like to seek your advice for a technical problem which I am attempting to solve.

Scenario:
Let's say we have two tables.
Table-A
Table-B

Table-A is the source table while Table-B is the target table.

Using ETL, we are copying data from Table-A to Table-B.

Say Table-A have columns named
Column1
Column2
Column3

Then Table-B might have columns like
ColumnA-1
ColumnA-2
ColumnA-3
ColumnB-Extra1
ColumnB-Extra2

What I mean is, Table B will have columns which DIRECTLY maps to column in Table-A but it might have different names + Table B have some additional columns.

Now let's come to what I want to achieve:
I want to compare data in Table-A and Table-B using Excel macro, stored procedure or any other possible way.

Currently I could think of following:

I will have one worksheet which will list name of tables Table-A and Table-B and mappings between their columns. Something like

Table-A | Table-B
Column1 | ColumnA-1
Column2 | ColumnA-2
Column3 | ColumnA-3

Now using an excel macro, I will read this information, form a select query and fetch data from Table-A as well as Table-B and compare them.

To compare, I will basically convert all the values to ‘string’ type and see if they are exactly equal.

The above mentioned idea appears quite reasonable to me, but I wonder whether there are any other ‘BETTER’ ways or any things which I might be missing?

Other relevant information:
I am on SQL Server 2000.
I want to write a generic functionality because I want to compare 80 source tables with 80 target tables.
I know how to use Excel macro. Stored Proc/Functions in SQL Server.
I wondered whether we can utilize Access in some way. [Can I hear something from Albert??? :)]

Your suggestions/ideas/thoughts are really appreciated!
Regards,
JD

JD
Friday, July 18, 2003

You might also might to keep it in a database to eliminate Excel.


tblSchema where
TableSetID, TableName1, TableName2, TableFieldName1,
TableFieldName2

select *
from tblSchema
order by TableSetID

do a cursor building a comparison query using all the field mappings per tableset.

Li-fan Chen
Friday, July 18, 2003

You don't really need Excel unless you are trying to build a comparison success/failure report and chart it.

Li-fan Chen
Friday, July 18, 2003

Hi

Li----, Thanks for your response.

I am sorry but I really don't understand your tblSchema response. Could you explain it in bit detail for me?

Also, I would like to reiterate that I don't want to compare only ONE table, I want to write a generalized procedure which can take two tables, their column names and compare EACH ROW.

As an output, it should only list down, the rows where data in two tables are not matching.

Btw, I guess there might be some ready made tool which can accomplish similar thing. Any suggestions?

Regards,
JD

JD
Friday, July 18, 2003

Well stick with what you are doing.. I am just offering an alternative that's replicating your mapping fields (in Excel) with mapping fields in (SQL)...

Basically you just have to do this to compare your tables.. LEFT OUTER JOINS looking for non-matchables and RIGHT OUTER JOINS looking for non-matchables.. this assumes all your values are unique and can't preserve the number of counts. Assuming the order has been preserved somehow, you can always use a cursor to iterate over both table and compare for datas where redundate values have to be verified as being the same.

Li-fan Chen
Friday, July 18, 2003

JD,

I have onece developed a few stored procedures and scripts in SQL server that allow you to compare data in tables, in some scenarios. If you are interested, I might send them to you by email.

Alexander Chalucov (www.alexlechuck.com)
Friday, July 18, 2003

Assuming TA si TableA, TA.F1 is TableA's first field and so on then....

Select TA.F1, TA.F2 .. TA.Fn From TA LEFT JOIN TB ON TA.F1 = TB.FI AND TA.F2=TB.F2 ... TA.FN=TB.FB WHERE TB.F1 Is Null

... will return the record in TableA which are not present in Table B

... a RIGH JOIN and a WHERE clause of TA.F1 Is Null will return the records that are in B but not in A.

... a normal JOIN without a WHERE clause will return the records that are present in both tables.

You do not need Excel at all (or stored procedures for that matter) ...

Alexandru
Friday, July 18, 2003

Hi Alexandru,

I don't want just a number of rows which are not present in one or the other table.

I want to check the row, value by value, and point out discrepancies.

and Alexander, I have sent you mail. It would be great if you can pass on your scripts to me, it will be good starting point! :)

Regards,
JD

JD
Friday, July 18, 2003

You cannot beat MS-Access for doing stuff like that.
Just link to your SQL-Server tables and run the following script:

Sub compareTables()

Dim rs1 As Recordset
Dim rs2 As Recordset
Dim bInserted As Boolean
Dim bDeleted As Boolean

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM [Table-A] ORDER BY [Column1],[Column2],[Column3]")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Table-B] ORDER BY [ColumnA-1],[ColumnA-2],[ColumnA-3]")

Do While Not rs1.EOF And Not rs2.EOF
  bInserted = False
  bDeleted = False
  If rs1("Column1") < rs2("ColumnA-1") Then
    bInserted = True
  Else
    If rs1("Column1") > rs2("ColumnA-1") Then
      bDeleted = True
    Else
      ' Column1 and ColumnA-1 are equal
      If rs1("Column2") < rs2("ColumnA-2") Then
        bInserted = True
      Else
        If rs1("Column2") > rs2("ColumnA-2") Then
          bDeleted = True
        Else
          ' Column2 and ColumnA-2 are equal
          If rs1("Column3") < rs2("ColumnA-3") Then
            bInserted = True
          Else
            If rs1("Column3") > rs2("ColumnA-3") Then
              bDeleted = True
            End If
          End If
        End If
      End If
    End If
  End If
  If bInserted Then
    Debug.Print "Extra in Table-A: ", rs1("Column1"), rs1("Column2"), rs1("Column3")
    rs1.MoveNext
  Else
    If bDeleted Then
      Debug.Print "Extra in Table-B: ", rs2("ColumnA-1"), rs2("ColumnA-2"), rs2("ColumnA-3")
      rs2.MoveNext
    Else
      ' equal records
      rs1.MoveNext
      rs2.MoveNext
    End If
  End If
Loop

Do While Not rs1.EOF
  Debug.Print "Extra in Table-A: ", rs1("Column1"), rs1("Column2"), rs1("Column3")
  rs1.MoveNext
Loop
Do While Not rs2.EOF
  Debug.Print "Extra in Table-B: ", rs2("ColumnA-1"), rs2("ColumnA-2"), rs2("ColumnA-3")
  rs2.MoveNext
Loop

rs1.Close
rs2.Close

End Sub

Eric Hop
Sunday, July 20, 2003

Thanks for code snippet Eric!

Though I found the solution in Excel only!

VBA capabilities always surprises me! :)

Regards,
JD

JD
Sunday, July 20, 2003

Well... I then suppose you have an identifier on the tables ... is this assumption correct? If yes, you still don't need VBA (Excel).

Alexandru
Monday, July 21, 2003

*  Recent Topics

*  Fog Creek Home