Comparing two identical tables

[scre...@my-deja.com]

Hi,

I am doing testing of a reporting module in our application. The
reporter runs against the database and stores the results in a table.
Lets name it table A. The data in table A is changed/refreshed everytime
the report that creates it is run. I also have a static table that says
what the results should be. Lets name it table B. The data in table B is
kept static.

I would like a procedure or algorithm that compare the data in two
tables A and B. If it is identical it would return a TRUE. If it is not
identical it would return a FALSE. I can assume that table A and B has
the same structure. Since I have alot of tables to compare more than
100+ I don't want have to feed the algorithm the structure of the table.
This system table should be able to give the structure of the table(how
many columns, type etc?). And then use that information to traverse from
column 1 to column N. A count(*) from each table will tell how many rows
there are. Similary it would traverse from row 1 to row M. If they don't
have equal number of rows (or columns) that should automatically qualify
for a FALSE.

Example 1:

Table A: TableB:

c1 c2 c1 c2
r1 A B r1 A B
r2 C D r2 C D

In this case the algorithm would return a TRUE.

Example 2:

Table A: TableB:

c1 c2 c1 c2
r1 A B r1 A B
r2 C D r2 C E

In this case the algorithm would return a FALSE.

If there is anyone out there that have a basis for such a script to MS
SQL Server I would be most interested having a look at it.

Thank you!

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[Russell Bride]

In 6.5 you can get a list of column names for a table with the following
statement:

select t.name, c.name, c.colid from syscolumns c, sysobjects t
where t.id = c.id
and t.name = "yourtable"

Then you could built yourself the appropriate SQL statement with
a cursor and execute the result with the EXEC command.
/R

[Anthony Faull]

There's nothing like a good SQL challenge. I couldn't resist this one. You
solve this using UNION and COUNT.

e.g.
DECLARE @a INT, @b INT, @c INT
SELECT @a = COUNT(*) FROM A
SELECT @b = COUNT(*) FROM B
SELECT @c = COUNT(*) FROM ((SELECT * FROM A) UNION (SELECT * FROM B)) AS C
IF (@a=@b AND @b=@c)
PRINT 'Tables are identical'
ELSE
PRINT 'Tables differ'

Of course if you want to compare a whole span of tables automatically
you'll need a CURSOR and an EXEC statement.
e.g.

-- List the tables we wish to compare
CREATE TABLE CheckList (t1 VARCHAR(30), t2 VARCHAR(30), match CHAR(3) NULL)
GO

-- Compare tables, and place results in table CheckList
CREATE PROC ProcessChecklist AS
DECLARE cur CURSOR FOR (SELECT t1, t2 FROM CheckList) FOR UPDATE
OPEN cur
DECLARE @t1 VARCHAR(30), @t2 VARCHAR(30)
FETCH NEXT FROM cur INTO @t1, @t2
WHILE (@@FETCH_STATUS = 0) BEGIN
EXEC('
DECLARE @a INT, @b INT, @c INT, @match CHAR(3)
SELECT @a = COUNT(*) FROM '+@t1+'
SELECT @b = COUNT(*) FROM '+@t2+'
SELECT @c = COUNT(*) FROM (SELECT * FROM '+@t1+' UNION SELECT * FROM
'+@t2+') AS C
SELECT @match = (CASE WHEN (@a=@b AND @b=@c) THEN "Yes" ELSE "No" END)
UPDATE CheckList SET match = @match WHERE CURRENT OF cur
')
FETCH NEXT FROM cur INTO @t1, @t2
END
CLOSE cur
DEALLOCATE cur
GO

-- Create some test data
CREATE TABLE A (i INT)
CREATE TABLE B (i INT)
CREATE TABLE C (i INT)
CREATE TABLE D (i INT)
INSERT INTO A VALUES (1)
INSERT INTO A VALUES (2)
INSERT INTO B SELECT * FROM A
INSERT INTO C SELECT * FROM A
INSERT INTO D SELECT * FROM A
GO

-- Show some results!
INSERT INTO CheckList (t1,t2) VALUES ('A','B')
INSERT INTO CheckList (t1,t2) VALUES ('C','D')
EXEC ProcessChecklist
SELECT * FROM CheckList
GO

And Viola! We can compare any number of tables simply by popping their names
into table CheckList.

Regards,
Ant

<scre...@my-deja.com> wrote in message news:7pei0k$1f4$1...@nnrp1.deja.com...