I've been asked to cross reference some phone records with the SQL database. I've imported the spread sheet into a temporary table with the idea of checking the phone number and returning the name (either employee name or supplier name). I've been staring at this for 2 hours now and I'm a little confused as thow to actually achieve it...
A poor attempt I know, but this is what I've got so far...
DECLARE @Callee_Name VarChar(30)
BEGIN
INSERT INTO TempPhoneRec$([Callee Name])
VALUES (@Callee_Name, (
SELECT EmployeeName
FROM Empoyee
JOIN TempPhoneRec$ ON
(REPLACE(empPhoneRec$.Callee, ' ', '')) = (REPLACE(Employee.Phone_LL, ' ', ''))
OR
(REPLACE(empPhoneRec$.Callee, ' ', '')) = (REPLACE(Employee.Phone_Mobile, ' ', ''))
))
END
Any pointers or advice as to which would be the best way to achieve this...?
You are joining two tables Empoyee and TempPhoneRec$, yet there is no reference to TempPhoneRec$ in the join condition. You are referring to another table (?) empPhoneRec$ in the join condition. That seems wrong, and in fact should have given you an error when you try to run it.
Thanks for your reply, correct I hadn't joined the third table, I was trying to keep it simple to get the first part of it working.
No, the code above doesn't execute. Like I said it was a poor attempt and I was getting confused as to the best way to attempt it...
Many thanks
Dave
EDIT:
Doh...! Just noticed I was using INSERT instead of UPDATE. OK Getting a bit closer now...
UPDATE TempPhoneRec$
SET TempPhoneRec$.[Callee Name] = Employee.EmployeeName
FROM Employees
JOIN TempPhoneRec$ ON (REPLACE(TempPhoneRec$.Callee, ' ', '')) = (REPLACE(Employees.Phone_LL, ' ', ''))
OR
(REPLACE(TempPhoneRec$.Callee, ' ', '')) = (REPLACE(Employees.Phone_Mobile, ' ', ''))
-- Get data into #TEMP tables
SELECT [Source]='Employee', [Name]=EmployeeName, Phone_LL, Phone_Mobile
INTO #TEMP_Names
FROM Employee
INSERT INTO #TEMP_Names
SELECT [Source]='Supplier', SupplierName, Phone_LL, NULL
FROM Suppliers
--TODO Import from more sources
SELECT SomePKeyRef, Callee
INTO #TEMP_PhoneRec
FROM TempPhoneRec$
-- Clean up all phone numbers
UPDATE U
SET Phone_LL = REPLACE(Phone_LL, ' ', '')
, Phone_Mobile = REPLACE(Phone_Mobile, ' ', '')
FROM #TEMP_Names AS U
UPDATE U
SET Callee = REPLACE(Callee, ' ', '')
FROM #TEMP_PhoneRe
--TODO ADD More CleanUp statements as you discover that you need them!
-- Create indexes on #TEMP_Names and #TEMP_PhoneRec on the Phone Number column(s)
SELECT [Source], [Name], Phone_LL, Phone_Mobile, Callee, SomePKeyRef
FROM #TEMP_Names AS N
JOIN #TEMP_PhoneRec AS P
ON P.Callee = N.Phone_LL
UNION -- probably faster than using an OR in the JOIN
SELECT [Source], [Name], Phone_LL, Phone_Mobile, Callee, SomePKeyRef
FROM #TEMP_Names AS N
JOIN #TEMP_PhoneRec AS P
ON P.Callee = N.Phone_Mobile
