Check for differences in multiple databases... 1

[davism]

All,

What is the best or recommended approach for check to see if values are present in one or more different database tables? With one key element, I believe anyway and that is there may be some items based on certain criteria that are in both.

For instance, I have database TEST with table TESTA with columns of ID, DATEPROCESSED, TYPE. I have database MYTEST with a table MYTESTB where I have colums (ID, DATEUTILIZED, TYPE). NOTE: The DATEPROCESS and DATEUTILIZED are essentially representing the same thing just the databases are using different column name terminology.

Now, I may have the following example:

TEST

TESTA
1 2/10/2010 B
2 12/31/2009 C
3 2/15/2010 B

MYTEST

MYTESTA

1 1/10/2010 B
2 12/31/2009 C
3 4/15/2010 B

Now, I have something I need to check to see if it is a duplicate or already exists. And I have values, 1, 1/10/2009, B.

You see that it doesn't exist in TEST.TESTA but it does exist in MYTEST.MYTESTA. So, if I search I would expect it to show it being a duplicate.

Next I would have values of 2, 12/31/2009, C. That exists in TEST.TESTA and MYTEST.MYTESTA. So, if I search I expect it would show it being a duplicate.

Next, I have 2, 7/24/2010, B. If I searxch I expect it would not show as a duplicate.

What is the best manner or approach?

Create a temporary table and select from TEST.TESTA for a finding and MYTEST.MYTESTA for a find then if the temporary table has any rows then I know there is a duplicate and if not then I know it's not?

Or is there another way?

Any inform would be greatly appreciated.

Thanks

 

[gmmastros]

Are both databases attached to the same instance of SQL Server?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Yes, they are.

 

[gmmastros]

If I understand your question correctly, I think you want to use a full join. This will allow you to find data in EITHER table, and also determine which table it's in.

The following query is untested but it should work. Assuming your login has appropriate permissions to both databases, you should be able to run this from a query window.

Select Coalesce(db1.ID, db2.ID) As ID,
       Coalesce(db1.DateProcessed, db2.DateUtilized) As TheDate,
       Coalesce(db1.Type, db2.Type) As Type,
       Case When db1.id = db2.id then 'Both'
            When db1.id is null then 'In Database 2'
            When db2.id is null then 'In Database 1'
            End As WhereFound
From   Test.dbo.TestA As db1
       Full Join MyTest.dbo.MyTestA As db2
         On  db1.id = db2.id
         And db1.DateProcessed = db2.DateProcessed
         And db1.Type = db2.Type

If this works for you, and you would like me to explain it, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Actually, not so much working. By the way, I just want to see if it exists so like a SELECT COUNT(1) or something...

What's happening is that it's giving the equivalent of all 3 rows.

Remeber, I want to put some values in. There would need to be something in a WHERE statement (albeit, I know when joins it equates to and "=" in a WHERE. )

BTW, the dateprocessed is the same on your listing for each database. Just an FYI.

 

[gmmastros]

What do you need to know? Do you need to know if it exists in both db's. If not, do you need to know which one? Or do you just need to know if it exists anywhere?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

I just basically need to know if it exists anywhere in those db's based on the conditions.

A manner in which I described as a possibility is definitely there which is to create a temporary table.

Have one SQL load in the information into that table IF it is present or meets the conditions.

Do, the same on the second that checks to see if it's in the second but not the first.

Then do the same again if it's in both (TEST and MYTEST). However, it will fail if trying to add into the temporary table. But if it fails I know a record already exists and what put in there from the one of one of the SQL's before it.

However, what I'm not sure of is whether or not there is a better way.

Hope this helps explain some.

 

[gmmastros]

The exists function is very efficient, so I would suggest you use it. I don't see any reason why you would need to use a temp table for such a simple task. Temp tables are the best way to do things sometimes, but I don't think this is one of those times.

If this were my task, I would write a stored procedure to do the search. This allows you some more flexibility in the way you write the code, and also allows you to write more efficient code.

The stored procedure would look something like this:

Create Procedure FindMyData
    @ID Int,
    @TheDate VarChar(20),
    @Type VarChar(20)
As
SET NOCOUNT ON

Declare @InDB1 Bit, @InDB2 Bit

If Exists(Select 1 From Test.dbo.TestA Where ID = @ID And DateProcessed = @TheDate And Type = @Type)
	Set @InDB1 = 1

If Exists(Select 1 From MyTest.dbo.MyTestA Where ID = @ID And DateUtilized = @TheDate And Type = @Type)
	Set @InDB2 = 1

Select Case When @InDB1 = 1 And @InDB2 = 1 Then 'Both'
            When @InDB1 = 1 And @InDB2 Is NULL Then 'Database 1'
            When @InDB1 Is NULL And @InDB2 Is NULL Then 'Database 2'
            Else 'Not found'
            End As SearchResults

I'm reasonably sure you'll want to change the code I posted above. You'll want to change the parameters, specifically the data types. I chose varchar for the date and the type. You should make the parameters match the actual column's data type. You may also want to change the wording that appears in the case statement so that it suits your situation a bet better. You should be able to use the code I posted above as a template for writing your code.

Once you have the stored procedure working, you can call it like this...

Exec FindMyData 2,'12/31/2009','C'

I hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Yeah, that might work. I'll have to give that a shot when I can.

I'll let you know ASAP when I try it. Keep in mind, I am doing a very scaled down version my TEST and MYTEST. As some of it involves some of the items in TEST.TESTA are derived from what is in MYTEST.TESTA. In that situation I am doing an INNER JOIN. But the what you mentioned on the simplicity aspect just might work out fine.

Will let you know as soon as possible.

 

[davism]

Worked out very well! Thanks for bringing that up and very much appreciated.