CODEWITHSUNDEEP
sqlsql-serverselectinsert
Gribbler
Gribbler

Reputation: 434

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

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.

Upvotes: 2

Views: 4565

Answers (1)

gbn
gbn

Reputation: 432210

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)

Upvotes: 1

Related Questions