CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-insertprimary-key
user721126
user721126

Reputation: 143

Inserting from one table to another issue

I'm having trouble trying to get the following stored procedure to work. I have a table of user groups that consists of a user_id and a group_id. Both ids are setup as primary keys and not null. What I'm trying to do is insert into this table all users that currently are in a group list into this table. To make it more clear I'm trying to do the following:

Users that are in groupIds 50, 52, 53 insert them into group 40.

Currently the stored procedure gives me a PRIMARY KEY constraint 'PK_user_groups'. Cannot insert duplicate key in object ... The duplicate key value is (3, 40). When I look at the user_groups table I do see a record for 3, 40, but group 40 also contains users 5, 8, 10. I want users 5, 8, 10 to be added to group 40, but it fails due to user 3 already having a record.

Here is my stored procedure:

@targetGroupId int,
@groupList nvarchar(max)

DECLARE @list TABLE (groupId int);
    
INSERT INTO @list
SELECT * FROM SplitToInt(@grouplist, ',');

INSERT INTO user_groups(userId, groupId)
SELECT ug.userId, @groupId 
FROM user_groups ug 
INNER JOIN @list l ON ug.groupId = l.groupId
WHERE EXISTS (SELECT userId FROM users_groups WHERE groupId = l.groupId)

Upvotes: 0

Views: 40

Answers (1)

Charlieface
Charlieface

Reputation: 72194

You have two problems here:

  • You need to exclude all users already in group 40
  • You also don't want to add users twice if they are in multiple other groups.

We can solve the first problem with EXCEPT which will remove all existing users.

EXCEPT also solves the second problem because it implies DISINCT

INSERT INTO user_groups(userId, groupId)
SELECT ug.userId, @groupId 
    FROM user_groups ug 
    INNER JOIN @list l ON ug.groupId = l.groupId
EXCEPT   -- EXCEPT implies DISTINCT
SELECT userId, groupId
    FROM users_groups;

Aside: you should use a proper table-type parameter instead of string-splitting:

CREATE TYPE GroupList AS TABLE (groupId int NOT NULL);

CREATE PROC yourProcedureHere
    @targetGroupId int,
    @groupList IntList
AS
.....

Upvotes: 1

Related Questions