I have tried 2 variations of this query and cannot get it to work due to the fact I cant use "where email in". I get this error
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS".
I am trying to import records from the marketingmaster table that allows duplicate entries in the email column to the addresses table that has the email column as the primary key.
If I run the bottom half, I can find all the dups but cant seem to filter them when inserting them.
INSERT INTO cust_email.dbo.addresses (fname, lname, email)
SELECT p.fname, p.lname, p.email
FROM marketing.dbo.marketingmaster p
WHERE p.email !=''
AND p.email is not null
AND patindex('%@%',p.email) >0
AND patindex('%.%',p.email) >0
AND NOT EXISTS
(SELECT email, count(ID)as count FROM marketing..marketingmaster
WHERE email !=''
AND email is not null
AND patindex('%@%',email) >0
AND patindex('%.%',email) >0
GROUP by email
HAVING count(ID) > 1)
Thanks in advance for any help.