I have three tables, persons, email, and personemail. Personemail basically has a foreign key to person and email so one person can be linked to multiple email addresses. Also the email table has a field named primaryemail. This field is either 1 or 0. The primary email flag is used for pulling emails into reports/invoices etc.
There was a logic flaw in the UI that allowed users to set no primary email addresses for customers. I have closed the logic flaw but I need a script to force a primary email address for any customer that doesn't have one set. It was decided to set the primary email address to the lowest value for emailid (the primary key in the email table). Below is the script that was written and it works but it is very expensive to run and may cause locks for end users while running. The software is deployed in multiple time zones so even if we run it during the lowest usage time we need it to run as fast as possible.
Here is the current script. It has temp tables and a while loop so you can see it can really be improved upon. My SQL skills need polishing so I am putting it out here for suggestions.
CREATE TABLE #TEMP(PERSONID INT, PRIMARYEMAIL INT,FLAG INT)
CREATE INDEX IDX_TEMP_PERSONID ON #TEMP(PERSONID)
CREATE TABLE #TEMP2(PERSONID INT,PRIMARYEMAIL INT)
CREATE INDEX IDX_TEMP2_PERSONID ON #TEMP2(PERSONID)
--Grab all the person id's that have at least one email addresses that is not primary in the db, also set a flag for the while loop
INSERT INTO #TEMP
SELECT PE.PersonID, E.primaryEmail ,0
FROM Account.tbPersonEmail PE WITH (NOLOCK)
LEFT OUTER JOIN Account.tbEmail E ON E.EmailID=PE.EmailID
WHERE E.primaryEmail=0
--Grab all person ID's that have at least one email address that is primary.
INSERT INTO #TEMP2
SELECT PE.PersonID, E.primaryEmail
FROM Account.tbPersonEmail PE WITH (NOLOCK)
LEFT OUTER JOIN Account.tbEmail E ON E.EmailID=PE.EmailID
WHERE E.primaryEmail=1
--SELECT * FROM #TEMP2
--Remove any customers that already have a primary email set.
DELETE FROM #TEMP WHERE PERSONID IN (SELECT DISTINCT PERSONID FROM #TEMP2)
--Debug line to see how many customers are affected.
--SELECT * FROM #TEMP
--Perfom a while loop to update the min email ID to primary.
DECLARE @INTFLAG INT
DECLARE @PERSONID INT
SET @INTFLAG = (SELECT COUNT(*) FROM #TEMP)
--SELECT @INTFLAG
WHILE (@INTFLAG > 0)
BEGIN
SET @PERSONID =(SELECT TOP(1) PERSONID FROM #TEMP WHERE FLAG=0)
UPDATE Account.tbEmail SET primaryEmail=1 WHERE EmailID=(SELECT MIN(EMAILID) FROM Account.tbPersonEmail where PersonID=@PERSONID)
--Update the flag on the #temp table to grab the next ID
UPDATE #TEMP SET FLAG=1 WHERE PERSONID=@PERSONID
--Reduce the intflag variable that the loop is running off of.
SET @INTFLAG=@INTFLAG-1
END
DROP TABLE #TEMP
DROP TABLE #TEMP2