带子查询的SQL插入-检查是否不存在 [英] SQL Insert Into w/Subquery - Checking If Not Exists

问题描述

假定两个表-UserRole(UserRoleID，UserID，RoleID)和UserPermission(UserPermissionID，UserID，PermissionID，RegionID).

Assume two tables - UserRole (UserRoleID, UserID, RoleID) and UserPermission (UserPermissionID, UserID, PermissionID, RegionID).

我有以下带有子查询的INSERT，可为每个具有特定RoleID的UserID在UserPermission中创建新行:

I have the following INSERT w/subquery to create new rows in UserPermission for every UserID with a certain RoleID:

INSERT INTO UserPermission (UserPermissionID, UserID, PermissionID, RegionID)
SELECT NEWID(), UserID, @PermID, NULL
FROM UserRole WHERE RoleID = '<specific guid>'

如何检查要插入的行在数据库中是否不存在?我知道如何检查单个记录(如果NOT EXISTS(blah blah))，但是检查是否存在特定UserID和PermissionID(始终为@PermID-特定GUID)的组合的最佳方法是什么?排在UserPermission表中?

How can I check to see whether the rows I'm inserting don't already exist in the database? I know how to check for an individual record (IF NOT EXISTS(blah blah)), but what would be the best way to check whether a combination of a particular UserID and PermissionID (always going to be @PermID - a particular GUID) exist in a row in the UserPermission table?

推荐答案

应该可以这样实现:

INSERT INTO UserPermission (UserPermissionID, UserID, PermissionID, RegionID)
SELECT NEWID(), UserID, @PermID, NULL
FROM UserRole r
WHERE RoleID = '<specific guid>'
AND NOT EXISTS (SELECT * FROM UserPermission WHERE UserID = r.UserID AND @PERMID = PermissionID);

这篇关于带子查询的SQL插入-检查是否不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！