Solved

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

Posted on 2007-11-29
12
930 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

717 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