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)  Temp tables and using DISTINCT before insert

Author  Topic  bgc123 Starting Member 5 Posts Posted - 2001-08-01 : 14:29:15 Having a problem pulling truly distinct email addresses to insert into my email list from another table. What I'm trying to do is first query out distinct email addresses, insert them into a temp table, then query those records out for insert into my email list database. I need distinct as I have a nonclustered (unique) index on the email address field. Thanks for any help/suggestions. CREATE TABLE #UpdateEmail ( FirstName varchar(50), LastName varchar(50), EmailAddress varchar(50) ) INSERT INTO #UpdateEmail (FirstName, LastName, EmailAddress) SELECT DISTINCT EmailAddress, FirstName LastName FROM EmailList e WHERE (NOT EXISTS (SELECT EmailAddress FROM EmailLive)) AND (ListtAdd = 1) AND (ListStatus = 'ACTIVE') GROUP BY Email, FirstName, LastName HAVING (NOT (Email IN (SELECT e.Email FROM Member WHERE e.Email LIKE '00%'))) INSERT INTO EmailLive (FirstName,LastName,EmailAddress) SELECT FirstName, LastName, EmailAddress FROM #UpdateEmail WHERE NOT EXISTS ( SELECT EmailAddress FROM EmailLive ) DROP TABLE #UpdateEmail