Create rows when selected rows exist

Question

I haven't spent much time with SQL Server, and I can't seem to figure something out.

I have 3 tables; user, usergroup, group.

usergroup is a table that contains both a group id and a user id, where the group table just contains it's id and a name column. I am attempting to add a new row in the usergroup table for lets say if the user is already paired with name = x in the group table I want to also pair him with y in the group table.

I'm sorry if that doesn't make too much sense I am not really sure how to explain it but the query I've come up with so far is:

 Insert into usergroup (user_id, group_id, default_group)
 Select user_group.user_id from [user_group]
      join [group] g
      on user_group.group_id = g.id
      where g.name = 'someName';

This should return all the user ids that I want to add a specific group_id/default value to, but I am unsure how to insert them into the table I tried to do something like VALUES ( (Select statement above), "group_id_I_want_to_specify, 'false'));

Answer 1

Your INSERT statement is malformed; you may want to try something like:

Insert into usergroup (user_id, group_id, default_group)
Select ug.user_id, 1234 AS group_id, ug.default_group 
from [user_group] ug
  join [group] g on ug.group_id = g.id
where g.name = 'someName';

Replace 1234 in the query above with the id of the group you wish to add for each user who is a member of someName.

---

A fully flushed out test script for this, including the NOT EXISTS clause you referenced:

USE tempdb;

CREATE TABLE dbo.groups
(
    GroupID INT CONSTRAINT PK_groups 
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , GroupName VARCHAR(255)
);

CREATE TABLE dbo.usergroup
(
    UserID int
    , GroupID int
        CONSTRAINT FK_usergroup_groups__group_id 
        FOREIGN KEY REFERENCES dbo.groups(GroupID)
    , DefaultGroup bit 
        CONSTRAINT DF_usergroup__default_group DEFAULT ((0))
    , CONSTRAINT PK_usergroup PRIMARY KEY CLUSTERED (UserID, GroupID)
);

INSERT INTO dbo.groups (GroupName)
VALUES ('test 1');

INSERT INTO dbo.groups (GroupName)
VALUES ('test 2');

INSERT INTO dbo.usergroup (UserID, GroupID, DefaultGroup)
VALUES (1, 1, 1);

DECLARE @NewGroupID INT;
DECLARE @ExistingGroupName VARCHAR(255);
SET @ExistingGroupName = 'test 1';
SET @NewGroupID = 2;

INSERT INTO dbo.usergroup (UserID, GroupID, DefaultGroup)
SELECT ug.UserID, @NewGroupID AS GroupID, ug.DefaultGroup
FROM dbo.usergroup ug
  INNER JOIN dbo.groups g ON ug.GroupID = g.GroupID
WHERE g.GroupName = @ExistingGroupName
    AND NOT EXISTS (
        SELECT 1
        FROM dbo.usergroup ug1
        WHERE ug1.UserID = ug.UserID
            AND ug1.GroupID = @NewGroupID);

As you may notice in the above code, I've used two-part naming to include the schema name when referencing table names. Also, I've added two variables to the INSERT INTO portion of code to simplify making changes.

I've added USE tempdb; - once SQL Server is restarted tempdb is automatically recreated; effectively dropping the tables I create in this code.

I'm also not calling the group table group, since that is a reserved word. Using it in code will be a recipe for confusing down-the-road. I've named it groups in my code above, since that more accurately reflects the content of the table.

Also, I've renamed several of your columns to more accurately reflect the content of those columns. For instance, you have an id column in the group table. I've renamed it GroupID, and used that nomenclature everywhere. This simplifies debugging.

Answer 2

Right off the bat, if you're targeting a column insert ( insert table ( col_1, col_2 ) ), you need to specify an insert value for both columns. From the posted syntax, it appears you're targeting 3 columns ( user_id, group_id, default_group ) but only inserting 1 explicit value ( user_group.user_id ).

To start, I recommend commenting out the insert portion of the syntax. This will allow you to fiddle around with your select statement until it returns only the data you want to insert. The values qualifier is not needed when using select insert method.

I'd recommend checking out the posted lit and giving it a go. Then edit your question accordingly if the insert fails. Good luck!