Loop in Sql query (While , For)

Question

I have a table for Users and another for Role and a table between them Access

How can I add a role to all of users in a SQL query?

Table User:

ID  Name ...
1
5
9
10
11

Table Role :

Id ....
10
11
12

Table Access:

UserID    RoleID
1           10
2           10
3           10
4           10

I want to do it with Loop, means from row 1 of User to end

Update :

I am using:

DECLARE @roleId int

SET @roleId = 79

INSERT INTO Access (Users.code, @roleId)
   SELECT code, @roleId 
   FROM Users

I get an error:

Incorrect syntax near '@roleId'.

Matthew Haugen · Accepted Answer · 2014-07-13 04:28:08Z

2

DECLARE @roleId ...;

INSERT INTO Access (UserID, RoleID)
SELECT ID, @roleId
FROM Users

Then you could also add a where if you wanted to. It doesn't sound like you want to so I won't give an example, but you can imagine how this query could be expand to do more complicated things.

If you've got the appropriate unique key, you might want to do something like:

DECLARE @roleId ...;

INSERT INTO Access (UserID, RoleID)
SELECT ID, @roleId
    FROM Users
    WHERE ID NOT IN (SELECT UserID FROM Access WHERE RoleID = @roleId)

You could also add a user to all roles by switching it around and doing something like:

DECLARE @userId ...;

INSERT INTO Access (UserID, RoleID)
SELECT @userId, ID
FROM Roles

answered Jul 13, 2014 at 4:28

Matthew Haugen

13.4k5 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matthew Haugen Over a year ago

I don't understand what you're trying to do in your ...Access (Users.code,...). That's a syntax error.

Matthew Haugen Over a year ago

Just use INSERT INTO Access (UserID, ... as I said in my answer. Don't specify Users.code.

Matthew Haugen Over a year ago

@user3780058 have you resolved your problem? I see this is still in the unanswered queue. Please accept my answer if it helped you, and if it hasn't then I'd love to continue to help.

Collectives™ on Stack Overflow

Loop in Sql query (While , For)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related