Compare Two Tables

  • I need help with the SQL query to comare two tables in two seperate databases on the same server. The tables have FirstName, LatName and Email as common fields.

  • Hi,

    You need to fully qualify your table names when comparing accross databases. i.e. DatabaseName.owner.table

    'owner' will probably be dbo.

    Write a query similar to the following and see how you go:

    select * from database1.dbo.firsttable where LastName in (select LastName from database2.dbo.secondtable)

    That query will show all common records according to lastname. Once you have something like that working feel free to expand on what else you need to achieve.

    Bevan

  • Awesome, worked like a charm.

  • AFIFM (4/6/2009)


    I need help with the SQL query to comare two tables in two seperate databases on the same server. The tables have FirstName, LatName and Email as common fields.

    I have to ask, what are you going to do when you find out that they're equal or not?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • AFIFM (4/6/2009)


    I need help with the SQL query to comare two tables in two seperate databases on the same server. The tables have FirstName, LatName and Email as common fields.

    When you say compare, you want to find the records which are not in another table or find teh common records or !!

    In either case If you use FULL OUTER JOIN it would really help you to understand your data in this case.

  • Table 'ClientsDB.Users' is in 'MyClientsDB' database and table 'MarketingDB.Users' is in 'MyMarketingDB'. Table 'ClientsDB.Users' has 12,000 records that I want to copy to 'MarketingDB.Users'. I got the number of records (12,000) by doing the compare.

    Now, I just found that the one of the fields in 'ClientsDB.Users' is [VARCHAR(50)] and the equivalent field in 'MarketingDB.Users' is [int]. So how do I make the change from VARCHAR to int.

  • When you say compare, you want to find the records which are not in another table or find teh common records or !!

    In either case If you use FULL OUTER JOIN it would really help you to understand your data in this case.

    I would to try different ways so if you can send a sample for FULL OUTER JOIN that would be great. By the way, I was trying to find which records in table A that not in table B so I can copy the what is not in A to B.

  • I have to ask, what are you going to do when you find out that they're equal or not?

    I was trying to find which records in table A that not in table B so I can copy those to table B.

  • You need to use the CAST function:

    http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

    Something like:

    select cast(users as int) from clientsDB.dbo.firsttable where cast(users as int) not in (select users from MarketingDB.dbo.secondtable)

    Then to do an insert:

    insert MarketingDB.dbo.secondtable (users, nextColumn, otherColumns)

    select cast(users as int), nextColumn, otherColumns from clientsDB.dbo.firsttable where cast(users as int) not in (select users from MarketingDB.dbo.secondtable)

    The above code is just so you get the idea. You need to ensure all your column names are listed when you select from your source table and that they correspond to the destination table.

    Bevan

  • AFIFM (4/7/2009)


    Now, I just found that the one of the fields in 'ClientsDB.Users' is [VARCHAR(50)] and the equivalent field in 'MarketingDB.Users' is [int]. So how do I make the change from VARCHAR to int.

    EX: ALTER TABLE EMPLOYEE_WORKING_HRS ALTER COLUMN YR VARCHAR(4)

    This is the way you can change the datatype. Even if you convert would the data still be the same?? Coz one column is varchar there is a potential that this contains characters and another one is INT..and this strictly contains the numbers. Is this domain type??

  • Do I have to use CAST/CONVERT when I change data type from [VARCHAR] to [int]? My VARCHAR has only values of "1", "2", "3", .... "8". I read something about Implicit and explicit conversion and that I do not have to use CAST or CONVERT in this case, I just need to confirm.

    http://doc.ddart.net/mssql/sql70/ca-co_1.htm

  • Is this domain type??

    No it is not. One of the legacy applications (A) uses 'Access_Level' as VARCHAR which only uses "1", "2", "3",....."8". The second application (B) uses the 'Access_Leval' as integer. I need to copy the records from (A) to (B) where is no matching records. The challange is that I need to do that on regular basis until I build a System of Records applications that can be used across the organization for access to company sites. Currently I have 3 sites that each with a seperate sign-in.:w00t:

    I just want to add that this is a great board and the help i am getting is unbelievable and it is really appreciated.:-)

  • You are right, it will convert automatically. I believe it will attempt to convert the varchar to an int.

    The explicit conversion is useful as it will catch any bad data and reminds anyone looking at the code what it is doing.

  • until I build a System of Records applications

    I would like to take this issue into a different direction. As I mentioned above, I am in the planning phase towards building a Single Sign On (SSO) application to handle users’ registration for the organization several web sites. My intention is to build the system using the System of Records (SOR) database which will have some users’ information and a localized database for each site. I am doing this for several reasons, one of which is that the SOR will have to be hosted internally because I am using proprietary software. Another reason is that each web site focuses on different type of information, which is collected from the visitors and I do not see a compelling reason to have all the information in the SOR database as it will be very flat. Having said that, my manager wants to use one internal database and I am trying to convince her with the opposite.

    Do you have any input on this? What is the argument for one case over the other? If this question does not belong on this forum, please let me know and I will move it.

  • AFIFM (4/7/2009)


    Is this domain type??

    No it is not. One of the legacy applications (A) uses 'Access_Level' as VARCHAR which only uses "1", "2", "3",....."8". The second application (B) uses the 'Access_Leval' as integer. I need to copy the records from (A) to (B) where is no matching records. The challange is that I need to do that on regular basis until I build a System of Records applications that can be used across the organization for access to company sites. Currently I have 3 sites that each with a seperate sign-in.:w00t:

    I just want to add that this is a great board and the help i am getting is unbelievable and it is really appreciated.:-)

    I've not read all the posts on this thread, but why not just use replication for this?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply