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

Question

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?

I think this could be [a duplicate](https://stackoverflow.com/a/25971/685760) — Mr Moose, Jan 28 '19 at 15:08

huMpty duMpty · Accepted Answer · 2019-01-28T17:46:15.963

1

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)

edited Jan 28 '19 at 17:46

answered Jan 28 '19 at 15:08

huMpty duMpty

14,346
14
60
99

The `cross join` should be for the `permissions` table. If you can update your answer to show that, I'll accept it as this worked beautifully. – Zephyr Jan 28 '19 at 15:19
@Zephyr Thanks for pointing that out. Updated the table name! – huMpty duMpty Jan 28 '19 at 17:46
Thank you for your help! – Zephyr Jan 28 '19 at 17:48

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

1 Answers1