CODEWITHSUNDEEP
sql-server
Zephyr
Zephyr

Reputation: 10263

How to insert multiple values from one table into another table?

I have three tables in SQL Server:

Users Table:

[user_id]       [other_columns]

Permissions Table:

[permission_id] [other_columns]

Users_Permissions Table (a many-to-many join table):

[user_id]       [permission_id]

I need to apply the same permissions to multiple users at once and insert the user_id and permission_id into the users_permissions table.

For example, I need to link all users in the users table to permission_id #5. I can do this individually with this code:

INSERT INTO users_permissions (user_id, permission_id) VALUES (1, 5);

But how would I do that in a batch for all users in the users table?

Bonus: Is it possible to also include multiple permission_id for each of those users? So if I want to apply insert 8 permissions for all 50 users, how would I do that?

Upvotes: 0

Views: 108

Answers (1)

huMpty duMpty
huMpty duMpty

Reputation: 14470

INSERT INTO users_permissions (user_id, permission_id) 
SELECT UserId, 5
FROM User

To do multiple you can use cross join. Below will insert permission 1 to 4 for each user

 INSERT INTO users_permissions (user_id, permission_id) 
 Select UserId, PermissionId
 From User
        Cross Join permissions 
 Where PermissionId in (1,2,3,4)

Upvotes: 1

Related Questions