How to insert a row into a table for every userid-CodePudding

In MSSQL I have a table codes with columns userid and code. There are many rows in a table already like

userid | code 
1      | A
2      | A
3      | B
4      | A
4      | B

I would like to insert B in code column for every userid that doesn't have it. in my examle it would be for userid 1 and 2.

How to dynamic insert this?

INSERT INTO 
    codes (userid, code) 
VALUES 
    (1, B), 
    (2, B);

Expected result is:

userid | code 
1      | A
1      | B
2      | A
2      | B
3      | B
4      | A
4      | B

CodePudding user response：

We can try using the following INSERT INTO ... SELECT:

INSERT INTO codes (userid, code)
SELECT userid, 'B'
FROM (SELECT DISTINCT userid FROM codes) t
WHERE NOT EXISTS (
    SELECT 1
    FROM codes c
    WHERE c.code = 'B' AND
          c.userid = t.userid
);

The subquery aliased as t will generate a unique set of all userid across the entire table. Some kind of distinct operation is needed, as a given missing userid for code B could appear more than once across other codes.

CodePudding user response：

You can use the EXCEPT operator:

INSERT INTO dbo.codes (userid,code)
SELECT DISTINCT userid, 'B' as code
FROM dbo.codes 
EXCEPT
SELECT userid,code
FROM dbo.codes
WHERE code='B';

CodePudding user response：

While this answer is logically equivalent to the WHERE NOT EXISTS answer, you should favour that answer because the LEFT JOIN may exhibit poorer performance in non trivial cases.

INSERT [codes]
SELECT
          L.[userid],
          'B'
    FROM
          [codes] L
       LEFT JOIN
          [codes] R
              ON R.[userid] = L.[userid] AND R.[code] = 'B'
    WHERE
          [R].[userid] IS NULL;