如何检查表变量是否具有ID

我有以下表格变量

DeclARE @insertedTable Table (ServiceId INT)

我通过一些SQL查询向其中插入ServiceId

现在我需要通过交叉联接BranchServices将值插入@insertedTable

我写了以下SQL查询

INSERT INTO BranchServices
    SELECT b.BranchId,its.ServiceId,CASE WHEN(SELECT MIN(Sortvalue) FROM BranchServices WHERE FkBranchId = b.BranchId) IS NOT NULL THEN 0 END
     FROM @insertedTable its
    CROSS JOIN Branch b where b.Isactive = 1;

@insertedTable表与ServiceId表不存在BranchServices时,上述查询工作正常。因为BranchServices的表ServiceId是主键。

因此,现在我需要检查,如果BranchServices表已经具有ServiceId,则不需要运行以下查询,否则,需要运行查询。如何使用合并?如果是的话,怎么写

INSERT INTO BranchServices
    SELECT b.BranchId,CASE WHEN(SELECT MIN(Sortvalue) FROM BranchServices WHERE FkBranchId = b.BranchId) IS NOT NULL THEN 0 END
     FROM @insertedTable its
    CROSS JOIN Branch b where b.Isactive = 1;
xiaozz3753 回答:如何检查表变量是否具有ID

总的猜测:

INSERT INTO BranchServices (BranchId,ServiceId,OtherColumn) --Don't know what the name of the third column is
SELECT b.BranchId,its.ServiceId,CASE WHEN (SELECT MIN(SortValue)FROM BranchServices WHERE FkBranchId = b.BranchId) IS NOT NULL THEN 0 END
FROM @insertedTable its
     CROSS JOIN Branch b
     LEFT JOIN BranchServices BS ON its.ServiceId = BS.ServiceId
WHERE b.IsActive = 1
  AND BS.ServiceId IS NULL;

旁注,空格对于编写可读代码确实非常重要,我建议对它进行的使用。

本文链接:https://www.f2er.com/3152818.html

大家都在问