CREATE TABLE #User (userID INT PRIMARY KEY, email VARCHAR(100) NOT NULL);

CREATE TABLE #UserProfile (userID INT NOT NULL, fieldID INT NOT NULL, value VARCHAR(50) NOT NULL, PRIMARY KEY (userID, fieldID));

INSERT #User (userID, email) VALUES (1, 'x@hotmail.com');

INSERT #User (userID, email) VALUES (2, 'y@gmail.com');

INSERT #User (userID, email) VALUES (3, 'z@yahoo.com');

INSERT #UserProfile (userID, fieldID, value) VALUES (1, 1000, 'external');

INSERT #UserProfile (userID, fieldID, value) VALUES (1, 1001, 'something');

INSERT #UserProfile (userID, fieldID, value) VALUES (2, 1100, 'blah');

INSERT #UserProfile (userID, fieldID, value) VALUES (2, 1156, 'fish');

INSERT #UserProfile (userID, fieldID, value) VALUES (3, 900, 'spanner');

INSERT #UserProfile (userID, fieldID, value) VALUES (3, 1000, 'external');

INSERT #UserProfile (userID, fieldID, value)

SELECT Missing.userID, 1000, 'external'

FROM (

SELECT U.userID

FROM #User AS U

EXCEPT

SELECT P.userID

FROM #UserProfile AS P

WHERE P.fieldID = 1000

) AS Missing;