如果不存在值(仅针对某些条件),如何将其插入表中? (MS SQL Server)

Asked
Viewd4535

2

我对SQL还是很陌生,但需要编写查询才能执行以下操作。 使用MS SQL Server 2005。

配置文件定义中的配置文件定义
------ ------------------- ----------
ProfileID定义ID定义ID
ProfileType ProfileID定义类型
个人资料名称

在定义表中,定义类型可以是TypeA,TypeB ..... TypeZ。 我要确保对于特定的配置文件类型,ProfileTypeA 定义具有所有类型,即TypeA-> TypeZ。

但是表中已经存在一些类型,我不想重复。

所以它像
从Profile中选择ProfileID,其中ProfileType = ProfileTypeA
对于每个ProfileID
   如果定义类型A中不存在
   将TypeA插入定义
   插入ProfileID,将DefinitionID插入DefinitionInProfile

   ...重复TypeB,TypeC ...
结束
  1. 我需要获取ProfileType = ProfileTypeA

  2. 的所有实例
  3. 然后获取第一个Profile.profileID

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

  5. 然后为所有这些定义ID检查是否有一个名为'TypeA'的definitionType 如果不插入,则忽略它。 然后对“ TypeB”执行相同的操作,对typec重复上述操作。.typeZ

返回步骤2,获取下一个Profile.ProfileID,然后为该Profile ID重复3和4。

1 个答案

1

尝试一下:

 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)
 
  • I didnt get an error.

    I have edited my question hopefully to make it clearer.

    GribblerFebruary 04, 2009 10:49
  • I can see what you’re saying, but its a database that Ive been asked to modify and I cant change the structure.

    GribblerFebruary 04, 2009 11:33
  • You need a source of definitions to give the range of definitions. If you add deftypea to deftypez manually, then the CROSS JOIN works. It makes no sense to add deftypea to deftypez to definition for each profiletype… this is the point of DefinitionInProfile to deal with the permutations

    gbnFebruary 04, 2009 11:20
  • It doesnt insert the missing ‘typeA’ ‘typeB’ … into the Definition table?

    GribblerFebruary 04, 2009 11:00
  • I understood it to be that you want have all combination of profile and definition and defintioninprofile. On a 2nd reading, you want it only for profiletypea. Did you get an error? Or

    gbnFebruary 04, 2009 10:46