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

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..

mugseyAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
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
 
PFrogCommented:
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
 
mugseyAuthor Commented:
OK Thanks I will have a play with your solution
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
imitchieCommented:
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
 
PFrogCommented:
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
 
mugseyAuthor Commented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
 
imitchieCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<racimo, I have reservations doing that>>
Try and see...
0
 
imitchieCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
 
mugseyAuthor Commented:
Cheers guys - I am looking at your comments and will update you back asap
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.