Monday, September 10, 2012

ASP.Net Compare multiple fields from different tables or databases in SQL

Hack The Gibson
Let’s think about what we are trying to do here.  We have two different databases – Let’s call them Shipping and Customers.  Obviously, both have customer information in them.  However, the shipping department has their own database and customer relations has their own.  As with most things run by humans, the two databases have become somewhat out of sync.  Management thinks this is a problem and they want a “report” or list of the differences between the two systems.  We’ll keep it simple and use customerID as the primary key in both tables.  We’ll be looking for Customer Name, Address, City, State, and Zip out of both tables.  If there is a difference, we want to see the data presented in a way that we can see the values of both tables, that way we can decide which one is correct and sync the two accordingly.
Our simple select for the Customer table might look like:
Select
        customer_id
    ,    customer_first_nm
    ,    customer_last_nm
    ,    customer_street
    ,    customer_city
    ,    customer_state
    ,    customer_zip
From
    dbo.customers




Our simple select for the Shipping table might look like:



Select
        customer_id
    ,    ship_to_first_nm
    ,    ship_to_last_nm
    ,    ship_to_street
    ,    ship_to_city
    ,    ship_to_state
    ,    ship_to_zip
From
    dbo.Shipping




Fairly simple right?  Notice that the field names are not exactly the same.  Well, first order of business to is rectify that.  We want these two tables to mimic each other as closely as possible.  How do we do that?  The short route is to simply alias the column names.  However, we’ll use temporary tables so that you can see the full concept:

Let’s create the temporary table for the customers first:




Create Table #temp_customer
    (
            ID            INT
        ,    First_NM    varchar(50)
        ,    Last_NM        varchar(50)
        ,    Street        varchar(250)
        ,    City        varchar(50)
        ,    [State]        varchar(2)
        ,    Zip            varchar(5)    
    )




Even though my formatting is a little muffed in the code transfer, you can see that I have created the table.  Now we need to get the values into the table:



Insert
    Into
        #temp_customer
        (
            ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
        )

Followed by our select statement, it will populate the temp table.  The code should look like so:




Create Table #temp_customer
    (
            ID            INT
        ,    First_NM    varchar(50)
        ,    Last_NM        varchar(50)
        ,    Street        varchar(250)
        ,    City        varchar(50)
        ,    [State]        varchar(2)
        ,    Zip            varchar(5)    
    )Insert
    Into
        #temp_customer
        (
            ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
        )Select
        customer_id
    ,    customer_first_nm
    ,    customer_last_nm
    ,    customer_street
    ,    customer_city
    ,    customer_state
    ,    customer_zip
From
    dbo.customers
    

That was easy.  But now it is our Shipping tables turn.  With a little Cut ‘n Paste magic we can build that table just as easy:




Create Table #temp_shipping
    (
            ID            INT
        ,    First_NM    varchar(50)
        ,    Last_NM        varchar(50)
        ,    Street        varchar(250)
        ,    City        varchar(50)
        ,    [State]        varchar(2)
        ,    Zip            varchar(5)    
    )Insert
    Into
        #temp_shipping
        (
            ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
        )
    Select
        customer_id
    ,    ship_to_first_nm
    ,    ship_to_last_nm
    ,    ship_to_street
    ,    ship_to_city
    ,    ship_to_state
    ,    ship_to_zip
From
    dbo.Shipping

Now our entire code block should look like:




Create Table #temp_customer
    (
            ID            INT
        ,    First_NM    varchar(50)
        ,    Last_NM        varchar(50)
        ,    Street        varchar(250)
        ,    City        varchar(50)
        ,    [State]        varchar(2)
        ,    Zip            varchar(5)    
    )Insert
    Into
        #temp_customer
        (
            ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
        )Select
        customer_id
    ,    customer_first_nm
    ,    customer_last_nm
    ,    customer_street
    ,    customer_city
    ,    customer_state
    ,    customer_zip
From
    dbo.customers

Create Table #temp_shipping
    (
            ID            INT
        ,    First_NM    varchar(50)
        ,    Last_NM        varchar(50)
        ,    Street        varchar(250)
        ,    City        varchar(50)
        ,    [State]        varchar(2)
        ,    Zip            varchar(5)    
    )Insert
    Into
        #temp_shipping
        (
            ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
        )
    Select
        customer_id
    ,    ship_to_first_nm
    ,    ship_to_last_nm
    ,    ship_to_street
    ,    ship_to_city
    ,    ship_to_state
    ,    ship_to_zip
From
    dbo.Shipping







But wait, let’s think about this.  It is more plausible to think that there are going to be more customers in the customers database than the shipping database.  This being the case, if we do a compare, we’ll end up with a whole mess of customers that don’t exist in the shipping database thus, at the end of our “report” it will tell us that such.  However, we only want to know about values that exist in both.  So if we take ONLY the customers that exist in the shipping database, we should do an equal compare.  For this to happen, we’ll need to build the shipping table first, then compare the customer id’s from the customer table with the ones in the shipping table like so:



Create Table #temp_shipping
    (
            ID            INT
        ,    First_NM    varchar(50)
        ,    Last_NM        varchar(50)
        ,    Street        varchar(250)
        ,    City        varchar(50)
        ,    [State]        varchar(2)
        ,    Zip            varchar(5)    
    )Insert
    Into
        #temp_shipping
        (
            ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
        )
    Select
        customer_id
    ,    ship_to_first_nm
    ,    ship_to_last_nm
    ,    ship_to_street
    ,    ship_to_city
    ,    ship_to_state
    ,    ship_to_zip
From
    dbo.Shipping
    
Create Table #temp_customer
    (
            ID            INT
        ,    First_NM    varchar(50)
        ,    Last_NM        varchar(50)
        ,    Street        varchar(250)
        ,    City        varchar(50)
        ,    [State]        varchar(2)
        ,    Zip            varchar(5)    
    )Insert
    Into
        #temp_customer
        (
            ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
        )Select
        customer_id
    ,    customer_first_nm
    ,    customer_last_nm
    ,    customer_street
    ,    customer_city
    ,    customer_state
    ,    customer_zip
From
    dbo.customers
Where customer_id IN (Select ID From #temp_shipping)

Notice our where clause at the end of the statement.  This means we will only gather records where the customer id from the customers table is in the list of IDs from temp shipping.  Now that we have populated our temp tables, let’s look at the compare:




Select
            MIN([Table_NM]) As 'Table Name'    
        ,    ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
From
    (
        Select
                'Shipping' As 'Table_NM'
            ,    ID
            ,    First_NM
            ,    Last_NM
            ,    Street
            ,    City
            ,    [State]
            ,    Zip
        From
            #temp_shipping
        
        UNION ALL
        
        Select
                'Customer' As 'Table_NM'
            ,    ID
            ,    First_NM
            ,    Last_NM
            ,    Street
            ,    City
            ,    [State]
            ,    Zip
        From
            #temp_customer
    ) tmp
Group By
        ID
        ,    First_NM
        ,    Last_NM
        ,    Street
        ,    City
        ,    [State]
        ,    Zip
HAVING COUNT(*) = 1
Order By ID, [Table Name] 

-- ****************** Cleanup *******************
    Drop Table #temp_customer
Drop Table #temp_shipping

Let’s look at what we are doing here, we are selecting from our newly created temporary tables as a UNION ALL (combining the two tables).  Then we are grouping all the fields together of like values.  Which means that if they are of like values, they will be in groups of 2 instead of one.  Notice the HAVING clause.  If there is a value that is different, it will be 1 record.  I included the table name so that we can see the values of each and know where they came from so we can find the discrepancy.  Finally, we are ordering by the ID and table name.  So the power of the compare comes from the UNION ALL and our grouping. 

As a last bit, we need to clean up our temporary tables, as they will continue to exist until we drop them. 


We have just compared multiple values from two different tables/databases.


Hack The Gibson

Håþþ¥ .ñꆆïñg…

No comments:

Post a Comment