How do I insert into a table if a value does not exist, but only for certain criteria? (MS SQL Server)

Question

Hi, Im very new to SQL but need to write a query to do the following. Using MS SQL Server 2005.

Profile                  DefinitioninProfile         Definition
------                   -------------------         ----------      
ProfileID                DefinitionID                DefinitionID
ProfileType              ProfileID                   DefinitionType
ProfileName                                          

In the definition table, defintion Type can be TypeA, TypeB ..... TypeZ. I want to make sure that for a certain profile type, ProfileTypeA the Definition has all the Types, TypeA -> TypeZ.

But some of the types already exist in the table, and I dont want to have duplicates.

So its something like
SELECT ProfileID from Profile where ProfileType = ProfileTypeA
FOR EACH ProfileID
   IF NOT EXISTS IN Defintion TypeA
   INSERT TypeA into Definition
   INSERT ProfileID, DefinitionID into DefinitionInProfile

   ...repeat for TypeB, TypeC...
END

1. I need to get all the instances where ProfileType = ProfileTypeA

2. Then get the first Profile.profileID

3. Then check the DefinitioninProfile table to get a list of DefinitionIDs where the profileID = Profile.ProfileID

4. Then for all those Definition IDs check if there is a definitionType called 'TypeA' if not insert it, if there is ignore it. Then do the same for 'TypeB', repeat for typec, .. typeZ

Go back to step 2 and get the next Profile.ProfileID and repeat 3 & 4 for that profile ID.

Answer 1

Try this:

INSERT DefinitionInProfile 
    (ProfileID, DefinitionID)
SELECT
    P.ProfileID, D.DefinitionID
FROM
    --All permutations of P and D
    Profile P
    CROSS JOIN
    Definition D
WHERE
    --Edit (added 2 rows)
    --But filter and lookup type -> id
    P.ProfileType = ProfileTypeA
    AND
    --End edit
    --But not where the defid is already there for that profileid
    NOT EXISTS (SELECT * --or 1!!
        FROM
            DefinitionInProfile DP
        WHERE
            DP.ProfileID = P.ProfileID AND
            DP.DefinitionID= D.DefinitionID)