sql - 在不存在的地方插入 foreach x

Question

我有一个相当简单的数据库，由三个(相关)表组成:用户、权限和用户权限。基本前提很简单:创建用户时，权限表中的所有记录都会自动添加到具有默认值的 user_permissions 表中。一切正常。

但是，由于我目前正在开发中，因此我会继续添加新权限，当然现有用户不会拥有这些权限，因为这些新权限在创建时并不存在于权限表中。因此，我有了创建一个小存储过程以使用 user_permissions 表中当前不存在的所有权限自动更新 user_permissions 表的好主意。

所以，简而言之，我想做的是(伪代码)

For each user without x permission in user_permissions, insert into user_permissions user_id and permission_id

我不太确定如何从 SQL POV 执行此操作。我玩过加入和“不存在”但没有真正得到任何地方。

您可以在这里使用我的架构:http://sqlfiddle.com/#!3/b0761/3在此先感谢您的帮助!

编辑:模式:

CREATE TABLE users (
  user_id      int IDENTITY(1, 1) NOT NULL,
  user_name    varchar(255),
  PRIMARY KEY (user_id));

CREATE TABLE permissions (
  permission_id          int IDENTITY(1, 1) NOT NULL, 
  permission_name        varchar(255) NOT NULL, 
  PRIMARY KEY (permission_id));

CREATE TABLE user_permissions (
  user_id       int NOT NULL, 
  permission_id int NOT NULL, 
  value         tinyint DEFAULT 0 NOT NULL,  
  PRIMARY KEY (user_id, 
  permission_id));

ALTER TABLE user_permissions ADD CONSTRAINT FK_user_pe338140 
FOREIGN KEY (permission_id)
REFERENCES permissions (permission_id);

ALTER TABLE user_permissions ADD CONSTRAINT FK_user_pe405324 
FOREIGN KEY (user_id) REFERENCES users (user_id);

INSERT INTO users(user_name) values('test_username');
INSERT INTO users(user_name) values('test_username2');

INSERT INTO permissions(permission_name) VALUES('permission_1')
INSERT INTO permissions(permission_name) VALUES('permission_2')

INSERT INTO user_permissions(user_id, permission_id, value)
VALUES(1, 1, 1)

INSERT INTO user_permissions(user_id, permission_id, value)
VALUES(2, 1, 1)

编辑:到目前为止的查询

SELECT a.user_id, b.permission_id, 1 as 'value'
FROM USER_PERMISSIONS a right outer join 
PERMISSIONS b on a.permission_id = b.permission_id

Answer 1

insert into user_permissions (user_id, permission_id)
select
  u.user_id,
  p.permission_id
from
  users u
  cross join permissions p
where
  not exists (select 0 from user_permissions with (updlock, holdlock)
              where user_id = u.user_id and permission_id = p.permission_id)

引用:Only inserting a row if it's not already there