How to compare data in two tables?

In the beginning of this week I got a task from Susantha to modify a stored procedure. After the modification I had to test to compare the result sets before modification and after modification. That procedure only returns a data table. So I had so many test data on another table. Then I wrote a script to go through the script one by one. Actually I wanted to check whether those results are identical. I mean not the table structure. I wanted to perform a data comparison.

DECLARE @COUNTN INT;
DECLARE @COUNTO INT;
DECLARE @COUNTU INT;

CREATE TABLE #TEMPN
    (
        --Columns Here
    )

CREATE TABLE #TEMPO
    (
        --Columns Here
    )

CREATE TABLE #TEMPU
    (
        --Columns Here
    )

INSERT INTO #TEMPN 
    --Select From new table         

INSERT INTO #TEMPO
    --Select From old table     

SELECT  @COUNTU = COUNT(1)
FROM    #TEMPU
SELECT  @COUNTN = COUNT(1)
FROM    #TEMPN
SELECT  @COUNTO = COUNT(1)
FROM    #TEMPO

IF @COUNTN <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTN <> @COUNTU 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTU <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

DROP TABLE #TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU    

This will check whether your data in two tables are identical or not. if there’s any mismatch it will print error and show the both result sets. And there’s another issue on this script if one table returns NULL and the other table returns empty table it will take it as matched. Then I corrected it again.

DECLARE @COUNTN INT;
DECLARE @COUNTO INT;
DECLARE @COUNTU INT;

CREATE TABLE #TEMPN
    (
        --Columns Here
    )

CREATE TABLE #TEMPO
    (
        --Columns Here
    )

CREATE TABLE #TEMPU
    (
        --Columns Here
    )

INSERT INTO #TEMPN 
    --Select From new table         

INSERT INTO #TEMPO
    --Select From old table     

SELECT  @COUNTU = COUNT(1)
FROM    #TEMPU
SELECT  @COUNTN = COUNT(1)
FROM    #TEMPN
SELECT  @COUNTO = COUNT(1)
FROM    #TEMPO

IF @COUNTN <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTN <> @COUNTU 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTU <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

--New Code Block here

IF EXISTS(#TEMPO) BEGIN IF NOT EXISTS(#TEMPN) BEGIN SELECT * FROM #TEMPN SELECT * FROM #TEMPO PRINT 'ERROR' END END IF EXISTS(#TEMPN) BEGIN IF NOT EXISTS(#TEMPO) BEGIN SELECT * FROM #TEMPN SELECT * FROM #TEMPO PRINT 'ERROR' END END

DROP TABLE #TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s