Inserting unique values into a different tables if they don''t exists already.

Hi

I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.

Code Snippet

SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM Members M

INNER JOIN _MemberProfileLookupValues ML

ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND OptionID <> 6

When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID

I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately

ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.

Any ideas / help will be greatly appreciated. Please help.

Kind Regards

Carel Greaves

The following query insert the new members who option id 6 is not exist in the MemberProfileLookupValues table,

Code Snippet

SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT

M.MemberID

, '6'

, CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM

Members M

Where

NOT EXISTS

(

Select 1 From _MemberProfileLookupValues ML

Where M.MemberID = ML.MemberID And OptionID = 6

)

And M.Active = 1

|||

Thanks a lot.

That is exactly what i was looking for.

Kind Regards

Carel Greaves