CODEWITHSUNDEEP
sql-servert-sql
Jahanmir
Jahanmir

Reputation: 9

Loop in Sql query (While , For)

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'.

Upvotes: 0

Views: 123

Answers (1)

Matthew Haugen
Matthew Haugen

Reputation: 13286

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

Upvotes: 2

Related Questions