How to compare fields in 2 DBs

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

General SQL Server Forums

New to SQL Server Programming

How to compare fields in 2 DBs

Author

Topic

jatrix32
Starting Member

49 Posts

Posted - 2010-09-01 : 10:23:22

I want to compare a field from a table in DB1 to a field in a table in DB2.

Basically I am comparing phone numbers from table1 in DB1 to phone numbers in table2 in DB2, and I want all the phone numbers from table1 that are not in table2 to be displayed.

Im assuming a not equal will be used between the two fields being compared in order to return values that are not in both tables. But I am not sure how to structure the query since it is interacting between 2 databses.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 10:48:47

If the databases are on the server then you can JOIN to the tables using the three part notation

<databaseName>.<schema>.<table>

So if the databases are called db1 and db2 and the default schema is dbo then:

SELECT * FROM db1.dbo.table1

etc

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jatrix32
Starting Member

49 Posts

Posted - 2010-09-01 : 11:24:21

I am a bit new at this, I don't understand what you mean by default schema.

Also do i enter an AND statement for the db2 also?

SELECT * FROM db1.dbo.table1 AND db2.dbo.table2

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 11:56:29

Ok -- lets go back to basics.

Lets suppose that you have only one database. In this database you've got two tables. You haven't told me what they are called but I'm going to call them table1 and table2.

Both tables have a [phoneNumber] column in them.

If you wanted to find all [phoneNumber] entries that were in table1 but not in table2 you could do something like


SELECT
    t1.[phoneNumber]
FROM
    table1 AS t1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM table2 AS t2
        WHERE t2.[phoneNumber] = t1.[phoneNumber]
    )

This finds all phone numbers that are in table1 which ARE NOT in table2

Now because you have two different databases the query is very simlar - I'm calling your databases db1 and db2. It would look like


SELECT
    t1.[phoneNumber]
FROM
   db1.dbo.table1 AS t1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM db2.dbo.table2 AS t2
        WHERE t2.[phoneNumber] = t1.[phoneNumber]
    )

By schema I mean the middle part of the three part notation. A schema is like a group - think of it like this: tables belong to schemas. schemas belong to databases. The default schema is dbo.

Here's what the help for SQL server says about the 4 part naming convention:

quote:

Unless specified otherwise, all Transact-SQL references to the name of a database object can be a four-part name in the following form:

server_name .[database_name].[schema_name].object_name

Hope this helps.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jatrix32
Starting Member

49 Posts

Posted - 2010-09-01 : 12:08:00

This is a great Help Charlie. I do have another question though. In the statement where is says 'SELECT 1', what does the 1 signify? I am basically looking for records that did not import correctly. So I am matching the phone numbers from the original list (table = union_import) to phonenumbers in the master list (table = PHONENUMBERS). Based on your help abouve, my query looks like this:

SELECT sub_user_number
FROM union_import t1
WHERE (NOT EXISTS
(SELECT 1
FROM WENS.dbo.PHONENUMBERS AS t2
WHERE t2.[sub_user_number] = t1.[sub_user_number]))

But I am returning about 14 results, when i am fairly confident that there oare at least 400 records that did not import based on the logs and error code. What is this query doing incorrectly?

The import DB is WENS_IMPORT, the table is union_imoprt and the column is sub_user_number

The Master that I am checking against is WENS, the table is PHONEMUMBERS and the column is sub_user_number

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 12:27:21

the select 1 in an EXISTS / NOT EXISTS clause is just a common placeholder.

If you are using EXISTS / NOT EXISTS then you don't actually want to use any data returned in the clause you just care if a result set is returned or not. Some people use SELECT * etc. It basically doesn't matter what you SELECT inside an EXISTS clause as long as you SELECT <SOMETHING>

Your query looks ok to me.

If you don't like the results my advice would be to do a SELECT DISTINCT from each table separately (just the [sub_user_number]) and store the results in two files.

Then use beyond compare or something else to reassure yourself that the query is correct.

Regards,
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jatrix32
Starting Member

49 Posts

Posted - 2010-09-01 : 13:22:43

Thanks alot charlie, turns out this query worked perfectly. You have been extremely helpful!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources