SIMPLE SQL MATCH QUESTION--HELP!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SIMPLE SQL MATCH QUESTION--HELP!

Author

Topic

mbroad
Starting Member

1 Post

Posted - 2007-08-16 : 11:37:51

Hi-I am a newbie to SQL, so please bear with me. I am trying to run a SQL server statement which will match DB1 against DB2 finding only the NON MATCHES between the two (and writing to a third DB).
DB1 has azpprox 600,000 rows in it, DB2 has 160,000 rows.
This is code I am using, but it is only finding 1 non match (and writes a NULL row in 3rd DB).
See what you think:

SELECT DISTINCT FIN.NAME, FIN.ADDRESS, FIN.CITY,
FIN.STATE, FIN.ZIP, FIN.ZIP4, FIN.DATEOFBIRTH,
FIN.SEX, FIN.SID

into SOS_41_RERUN

FROM SOS_TABLE KEYN left outer join FINAL_FILE FIN

on FIN.SID = KEYN.SID
WHERE FIN.SID is null;
I truly appreciate your help!!

Mark Broadwell

ujb
Starting Member

8 Posts

Posted - 2007-08-17 : 00:41:09

Replace all your FIN. to KEYN. otherwise it will just post a NULL row because it is getting its data from the 'right' table of your left join

i.e.
SELECT DISTINCT FIN.NAME, FIN.ADDRESS, FIN.CITY,
FIN.STATE, FIN.ZIP, FIN.ZIP4, FIN.DATEOFBIRTH,
FIN.SEX, FIN.SID

to this...

SELECT DISTINCT KEYN.NAME, KEYN.ADDRESS, KEYN.CITY,
KEYN.STATE, KEYN.ZIP, KEYN.ZIP4, KEYN.DATEOFBIRTH,
KEYN.SEX, KEYN.SID

Here is my test data to show proof of concept...

DECLARE @SOS_TABLE TABLE ([NAME] INT, ADDRESS INT, CITY INT, SID INT)
DECLARE @SOS_41_RERUN TABLE ([NAME] INT, ADDRESS INT, CITY INT, SID INT)
DECLARE @FINAL_FILE TABLE ([NAME] INT, ADDRESS INT, CITY INT, SID INT)

INSERT @SOS_TABLE
SELECT 1,2,3,4
UNION ALL
SELECT 1,2,3,5 UNION ALL
SELECT 1,2,3,6 UNION ALL
SELECT 1,2,3,7

INSERT @FINAL_FILE
SELECT 1,2,3,4

INSERT INTO @SOS_41_RERUN
SELECT DISTINCT KEYN.[NAME], KEYN.ADDRESS, KEYN.CITY, KEYN.SID

FROM @SOS_TABLE KEYN left outer join @FINAL_FILE FIN

on FIN.SID = KEYN.SID
WHERE FIN.SID is null;

SELECT * FROM @SOS_41_RERUN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources