Solved

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

Posted on 2007-11-29
12
904 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now