SQL Sytax

[NotSQL]

Hi Guys,

I have a bit of SQL which stores information into temp tables however I’m sure there is a better way of do this. Any help or suggestions would be grateful.

SELECT *
INTO #EMAIL
FROM KeyCareClean
WHERE NOT EXISTS
(select *
from STUDENT
where Upper(KeyCareClean.EmailAddress) = UPPER(STUDENT.EMAIL_ADDRESS_FULL))



SELECT *
INTO #MOBILE
FROM #EMAIL
WHERE NOT EXISTS
(select *
from STUDENT
where UPPER(#EMAIL.FirstName) = UPPER(STUDENT.NAME_FORENAME)
AND UPPER(#EMAIL.LastName) = UPPER(STUDENT.NAME_SURNAME)
AND #EMAIL.MobileNo = STUDENT.DAYTIME_TEL)

DROP TABLE #EMAIL

SELECT *
INTO #DOB
FROM #MOBILE
WHERE NOT EXISTS
(select *
from STUDENT
where UPPER(#MOBILE.FirstName) = UPPER(STUDENT.NAME_FORENAME)
AND UPPER(#MOBILE.LastName) = UPPER(STUDENT.NAME_SURNAME)
AND #MOBILE.DOB = CONVERT(VARCHAR(10), STUDENT.DATE_OF_BIRTH, 101))


DROP TABLE #MOBILE
SELECT * FROM #DOB

 

[AndyInNC]

Try this:

SELECT *   [COLOR=green]-- avoid avoid AVOID "select *". Pick what you want returned[/color]
INTO #EMAIL
FROM KeyCareClean  
WHERE EmailAddress NOT IN [COLOR=green]-- specify here what it is you're looking for[/color]
  (select EMAIL_ADDRESS_FULL
    from STUDENT)


[COLOR=green]-- The following 2 approaches are for your second issue.
-- In this instance, my preference is the first approach.
-- (You might have to play with the second a little to make it work.)[/color]
SELECT *
INTO #MOBILE
FROM #EMAIL
WHERE RTrim(IsNull(FirstName, '')) + RTrim(IsNull(LastName, '')) + RTrim(IsNull(MobileNo, '')) NOT IN
  (select RTrim(IsNull(NAME_FORENAME, '')) + RTrim(IsNull(NAME_SURNAME, '')) + RTrim(IsNull(DAYTIME_TEL, ''))
    from STUDENT)

SELECT e.*
INTO #MOBILE
FROM #EMAIL e
     LEFT JOIN  STUDENT s
             ON s.NAME_FORENAME = e.FirstName
             AND s.NAME_SURNAME = e.LastName
             AND s.DAYTIME_TEL = e.MobileNo
WHERE s.NAME_FORENAME IS NULL

I think you should be able to apply these to the third statement in your code.

Good job explicitly dropping the temp tables. You may consider using table variables in place of the temp tables. There are certain trade-offs there, but overall they don't create an actual table the way a temp table does. Look into "DECLARE @EMAIL TABLE" for info on that. One downside is you can't use "Select Into".

 

[simian336]

You might also look up CTE in SS2005 and up...

Then you would not need to define temp tables at all.

Simi