1

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'));

2
  • 1
    I guess a better context would be, group is a list of certificates that a user has, I want to give every user who has certificate x, a new certificate y.
    – David
    Jul 31, 2015 at 14:20
  • Is it usergroup or user_group? Jul 31, 2015 at 15:09

2 Answers 2

2

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.

6
  • Thank you this seems to be basically what I wanted to do it is possible that the user already has certificate x and y, so is there anywhere I could do not exists to prevent it from trying to duplicate the key value.
    – David
    Jul 31, 2015 at 14:42
  • sure, add a AND NOT EXISTS (SELECT ... ) to the WHERE clause.
    – Hannah Vernon
    Jul 31, 2015 at 14:48
  • That seems simple enough however, I added, And not exists (select group_id from [user_group] where ug.group_id = 5119); This doesn't seem to work how I intended it too, 5119 is the cert I want to make sure not to duplicate.
    – David
    Jul 31, 2015 at 15:06
  • @David - I've added further details to my answer to show how you can not add duplicate rows to destination table.
    – Hannah Vernon
    Jul 31, 2015 at 15:33
  • Thank you so much at first glance this all appears to be working I will be sure to test it to make sure it does what I am intending, again thank you.
    – David
    Jul 31, 2015 at 15:49
1

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!

1
  • Thanks for your help I will be sure to read through that.
    – David
    Jul 31, 2015 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.