title: insert foreach x  where not exists
body: <p>I have a fairly simple database consisting of three (relevant) tables: users, permissions, and user permissions. The basic premise is simple: when a user gets created, all the records in the permissions table are automatically added to the user_permissions table with a default value. This is all working fine.</p>
  
  <p>However, as I'm currently in development, I continue to add new permissions, which of course existing users won't have since those new permissions didn't exist in the permissions table when they were created. So, I had the brilliant idea to create a little stored procedure to automatically update the user_permissions table with all the permissions not currently existing in the user_permissions table.</p>
  
  <p>So, in short, what I want to do is something like (pseudocode)</p>
  
  <pre><code>For each user without x permission in user_permissions, insert into user_permissions user_id and permission_id
  </code></pre>
  
  <p>I wasn't quite sure how to do this from an SQL POV. I played with joins and "not exists" but haven't really gotten anywhere.</p>
  
  <p>You can play with my schema here: <a href="http://sqlfiddle.com/#!3/b0761/3" rel="nofollow">http://sqlfiddle.com/#!3/b0761/3</a>
  Thanks in advance for the help!</p>
  
  <p>EDIT: Schema:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>EDIT: Query so far</p>
  
  <pre><code>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
  </code></pre>