如果某个值不存在，我该如何插入表中，但仅针对某些条件？ （MS SQL Server）

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

我是SQL的新手,但需要编写查询来执行以下操作。使用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.

在定义表中,定义类型可以是TypeA,TypeB ..... TypeZ。我想确保对于某个配置文件类型,ProfileTypeA定义具有所有类型,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

   我需要获取ProfileType = ProfileTypeA的所有实例

2. Then get the first Profile.profileID

   然后获取第一个Profile.profileID

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

   然后检查DefinitioninProfile表以获取其中profileID = Profile.ProfileID的DefinitionID列表

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

   然后对于所有这些定义ID,检查是否存在名为'TypeA'的definitionType,如果没有插入它,如果有忽略它。然后为'TypeB'做同样的事情,为typec重复,.. typeZ

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

返回步骤2,获取下一个Profile.ProfileID,并为该配置文件ID重复3和4。

1 个解决方案

#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)