Solved

how to do a cross check in t-sql - using if not exists

Posted on 2007-11-29
12
917 Views
Last Modified: 2008-02-01
I need to do a cross check on three tables to determine if the records don't exist in one table then insert them into a temporary table so I can import.

Here is my scenario

I have two tables that will be the source data

tblContact
contactid    int (pk)
forename    varchar(50)
surname     varchar(50)
email         varchar(256)
address1   varchar(100)
datecreated datetime


tbleContactDetails
contactid      int (pk)
isVolunteer1      int
isVolunteer2      int

Also I have a two table that will be were the destination data will go

tblaspnet_ExtendedMembership_UserInfo
userid            guid  (pk)
firstname      varchar(50)
lastname      varchar(50)
address1      varchar(50)


tblTableProfiles
userid            guid (pk)
isVolunteer1      int
isVoltunteer1      int


Now, I have done a data export from the source to the destination tables by passing the database records though my app business logic.

However the source tables have been updated so I need to check what records exist in the "old" tables (tblContact and tblContactDetails) that DO NOT EXIST in the new tables..

0
Comment
Question by:mugsey
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 18

Expert Comment

by:PFrog
ID: 20374778
I think the fastest method would be to left join and check for nulls.

i.e. (assuming contactid = userid)

  SELECT C.* FROM tblContact C LEFT JOIN tblaspnet_Extended)Membership_UserInfo A
    ON C.contactid = A.userid
  WHERE A.userid IS NULL

This will return everything in tblContct that does not have a matching record in tblaspnet...

0
 

Author Comment

by:mugsey
ID: 20374840
OK Thanks I will have a play with your solution
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20376673
I notice that one is user_id INT and the other is GUID, which I assume is auto generated from somewhere? try this

select * from tblContact
where not exists (
  select * from tblaspnet_ExtendedMembership_UserInfo
  where firstname = tblContact.forename
    and lastname = tblContact.surname
    and address1= tblContact.address1)

this chooses everything from Contact that does not have a matching entry (by firstname, lastname and address1).  you can tweak the match easily
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 18

Expert Comment

by:PFrog
ID: 20376871
Good point, well spotted!

The left join solution is still applicable, but you would indeed have to use different fields to link the tables

SELECT C.* FROM tblContact C LEFT JOIN tblaspnet_Extended)Membership_UserInfo A
    ON C.forename = A.firstname
    AND C.surname = A.lastname  --etc.
  WHERE A.userid IS NULL

It would be interesting to see the performance difference between the left join and the not exists methods... let us know what you find
0
 

Author Comment

by:mugsey
ID: 20377935
Thank you very much everyone for your help.  I am at home now but will reply back once I am in work tomorrow.   Keep lookkng because I have a busy day tomorrow with lots of sql queries.  Thanks again everyone

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20389352
<<However the source tables have been updated so I need to check what records exist in the "old" tables (tblContact and tblContactDetails) that DO NOT EXIST in the new tables..>>
An alternative way...
select * from tblContact where (forename + surname) not in
(select (TC.forename + TC.surname)
from tblContact TC inner join tblaspnet_ExtendedMembership_UserInfo TE on (TC.forename + TC.surname)  = (TE.firstname + TE.lastname) )

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20392353
racimo, I have reservations doing that, because instead of using available indexes on forename, surname and allowing hash matching, we're forcing the additional concat (a+b) operation on both tables unnecessarily
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20394333
<<racimo, I have reservations doing that>>
Try and see...
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20394415
Let me restate that. I know for a fact that it's slower, on MySQL, MSSQL and Oracle.  Check the query plans if you don't believe me.  Any function on a field invalidates it from Index usage.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20394455
<<Let me restate that. I know for a fact that it's slower, on MySQL, MSSQL and Oracle.  Check the query plans if you don't believe me.  Any function on a field invalidates it from Index usage.>>
The questionner's should try to see for himself what method best fits his index structure and current concurrency conditions... But I see what you are getting at and understand your concern.  I should have mentionned that this method probably works better with a composite index.  You know as I do that query plans are an estimate based on statistics and a specific index structure.  I do not recall the questionner precisely mentionning these...;0)
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20394464
Your query obviously has some merit, if in fact there exists both the indexes:
(forename + surname) on tblContact; and
(firstname + lastname) on tblaspnet_ExtendedMembership_UserInfo

Who knows, that may be the case if the comparison is often enough! (okay I don't really believe it)
0
 

Author Comment

by:mugsey
ID: 20410375
Cheers guys - I am looking at your comments and will update you back asap
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question