Comparing Two Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing Two Tables

Author

Topic

veebster
Starting Member

3 Posts

Posted - 2002-09-19 : 13:18:55

My problem…….

I have a table that contains about 10,000 contact details that is called TABLENEW. It has the following structure:

PERSONID (PK,varchar) – it is 6 digit alpha/numeric
FORENAME (varchar)
LASTNAME (varchar)
COMPANY (varchar)
ADDRESS1 (varchar)
ADDRESS2 (varchar)
PHONE (varchar)
EMAIL (varchar)

I also have a copy of the table that is older called TABLEOLD (it has exactly the same structure).

Some of the contact details have changed since the copy was made and I would like to run a query that will tell me which records were changed and which fields were changed.

i.e. record F93KFK (PERSONID) has changed forename,surname and email.

How can I do this?

Many thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-19 : 13:30:49


select
    personid,
    case
        when n.forename <> o.forename then n.forename + '->' + o.forename
        else NULL
    end as forenamechange,
    case
        when n.lastname <> o.lastname then n.lastname + '->' + o.lastname
        else NULL
    end as lastnamechange,
    case
        when n.company <> o.company then n.company + '->' + o.company
        else NULL
    end as companychange,
    case
        when n.address1 <> o.address1 then n.address1 + '->' + o.address1
        else NULL
    end as address1change,
    case
        when n.address2 <> o.address2 then n.address2 + '->' + o.address2
        else NULL
    end as address2change,
    case
        when n.phone <> o.phone then n.phone + '->' + o.phone
        else NULL
    end as phonechange,
    case
        when n.email <> o.email then n.email + '->' + o.email
        else NULL
    end as emailchange
from
    tablenew n
    inner join tableold o
        on (n.personid = o.personid and
            (n.forename <> o.forename or
             n.lastname <> o.lastname or
             n.company <> o.company or
             n.address1 <> o.address1 or
             n.address2 <> o.address2 or
             n.phone <> o.phone or
             n.email <> o.email))

Jay White
{0}

veebster
Starting Member

3 Posts

Posted - 2002-09-20 : 06:51:16

Thanks for the help!

I can run this in query analyzer but i can't save it as a view. Is this possible?

My code is


SELECT     o.UC, CASE WHEN n.forename <> o.forename THEN o.forename + '->' + n.forename ELSE NULL END AS forenamechange, 
                      CASE WHEN n.lastname <> o.lastname THEN o.lastname + '->' + n.lastname ELSE NULL END AS lastnamechange, 
                      CASE WHEN n.company <> o.company THEN o.company + '->' + n.company ELSE NULL END AS companychange, 
                      CASE WHEN n.address1 <> o.address1 THEN o.address1 + '->' + n.address1 ELSE NULL END AS address1change, 
                      CASE WHEN n.address2 <> o.address2 THEN o.address2 + '->' + n.address2 ELSE NULL END AS address2change, 
                      CASE WHEN n.email <> o.email THEN o.email + '->' + n.email ELSE NULL END AS emailchange, 
                      CASE WHEN n.Phone1 <> o.Phone1 THEN o.Phone1 + '->' + n.Phone1 ELSE NULL END AS Phone1change
FROM         tblDelegates n INNER JOIN
                      tblDelegates_Original o ON (n.UC = o.UC AND (n.forename <> o.forename OR
                      n.lastname <> o.lastname OR
                      n.company <> o.company OR
                      n.address1 <> o.address1 OR
                      n.address2 <> o.address2 OR
                      n.email <> o.email OR
                      n.Phone1 <> o.Phone1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources