November 6, 2006 at 8:47 am
Hi all,
What join can I use to retrieve rows from table A that do NOT exist in table B?
Furthermore, the tables are on two separate (SQL) servers - I presume I'd have to add server A as a linked server in Server B?
Thanks for your help,
Jay Bee.
November 6, 2006 at 9:03 am
Yes, a linked server is the way to go if you're going to do it regularly - although there are also ways of running ad hoc queries without creating a linked server. What you want is an OUTER JOIN:
SELECT * FROM Server1.DB1.dbo.Table1 a
LEFT OUTER JOIN Server2.DB2.dbo.Table2 b
ON a.MyCol = b.MyCol
WHERE b.MyCol IS NULL
That will give you everything on the first server that doesn't have a match on the second. Change to RIGHT OUTER JOIN and change b to a in the WHERE clause to do it the other way round. I've assumed the columns have the same name in both tables, but that doesn't have to be true.
Note that I've used SELECT *, but you'll want to specify which columns you want to return: use of SELECT * isn't good practice.
John
November 6, 2006 at 12:05 pm
That's cool, but what's the syntax if I need to run comparisons on multiple columns in multiple tables?
Secondly, I may need to insert the unmatched rows into the database, any idea how to use the query results as input for the insert?
Thanks,
Jaybee.
November 6, 2006 at 2:18 pm
It's better if next time you'll ask what you actually need from the beginning.
Just not to waste everybody's time.
INSERT INTO dbo.Table1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM Server2.DB2.dbo.View2 b
WHERE NOT EXISTS (select 1 from dbo.Table1 a
where a.Col1 = b.Col1 and a.Col2 = b.Col2)
View2 is a set of joins on the remote tables you are selecting from.
You may not create view and include all joins in this statement, but it will affect performance very badly.
_____________
Code for TallyGenerator
November 6, 2006 at 4:52 pm
Sorry to chime in so late, but this sounds a lot like something I was working on awhile ago.
Are you performing some sort of data drift analysis between servers?
If you are, I have a lot of code and thoughts on that.
Sergiy has a good, workable solution but I found that for bigger tables joining all columns is pretty slow. That being said, I never tried creating a view.
I found this performed better (but, do pelase try Sergiy's solution with the view because Sergiy's a very smart guy and has probably tried the idea below):
INSERT INTO dbo.Table1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
(
SELECT Col1, Col2, Col3
FROM LocalTable
UNION ALL
SELECT *
FROM OPENQUERY (lnkServer, 'SELECT Col1, Col2, Col3 FROM remoteTable')
)
GROUP BY Col1, Col2, Col3
HAVING Count(*) = 1
If you are doing data drift analysis/resolution please let me know, I spent a lot of time on a similar project and have a more or less universal solution.
SQL guy and Houston Magician
November 7, 2006 at 12:03 am
Robert,
I very definitely am, but the deadline for the project is in about 3 hours! It's really management's shortcoming, I'm simply trying to dig them out of a hole.
I will come back to this page between now and then though.
Cheers,
Jaybee.
November 7, 2006 at 10:31 am
Cool. If you like I'll send you what I've been working on. It is a proc that performs analysis and resolves drift (both one way and two way) It will generate code for any pair of tables (providing the schema matches)
The UNION...GROUP BY is used to create a shortlist of keys where drift exists . The actual resolution checksums rows and compares the checksums. Be careful when using checksum for this purpose if you have any FLOAT or REAL data types in your table. They tend to always report mismatched checksums.
Also, if you are performing a two way resolution (updating both the local and remote tables) be sure the update is always running locally relative to the table that is being updated. Otherwise performance will really suffer.
SQL guy and Houston Magician
November 7, 2006 at 4:16 pm
Cheers mate, I would very much like to see this code....
By the way, my attempts to save their necks failed...the software that uses the database runs into five figures just for one licence, and as you might imagine, the data within was encrypted, it made no sense unless queried by other processes which called on yet others, and of course vendor wasn't exactly chomping at the bit to explain precisely how the salient data is decrypted, or help me determine what tables, queried against what others, to run the join and yield the data I needed.
I even tried to dump the DB's to multiple .xls and do the work in Excel...no dice again, both DTS runs hung suspiciously similarly at around the 75% mark, the cunning swines...not that I blame them...I'd have done likewise if my new HDTV needed to be paid for!!
Still, send me what you have, I don't like challenges left unmet, I'll have a post-mortem stab and report back.
Jaybee.
November 7, 2006 at 4:26 pm
Yikes! I've been there a few times.
I suspect this isn't quite what you ment, but I have found this to be an extremely useful app.
http://www.securiteam.com/tools/6J00S003GU.html
Please tell me a little more about this project.
SQL guy and Houston Magician
November 8, 2006 at 12:40 am
Can you give some insight into how you run a query without creating a linked server?
Thanks.
November 8, 2006 at 5:30 am
Well, for example by using OPENROWSET - although I'm not sure this is what John meant. I'm using linked servers, so I don't know whether there are some other possibilities or not.
November 8, 2006 at 6:06 am
Ever done an update with that or an insert into ... select ... from?
I guess OPENROWSET is just for querying. This is the first time I tried it. I couldn't believe the use of mixed commas and semi-colons in the syntax.
November 8, 2006 at 6:37 am
Yes, the semicolons are pretty confusing... I'm not using OPENROWSET myself, except when I need to get data from some external source (Excel, Access), I just remembered that it works for SQL Server without linked server.
November 8, 2006 at 6:39 am
OPENROWSET is indeed what I meant. You're right, the syntax is messy, and that's why it's best to use linked servers if you have sufficient access to add one. From Books Online:
The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider.
John
November 8, 2006 at 6:44 am
Is there an OLE DB provider for Excel? What does it look like?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply