SQL INSERT INTO with Multiple SELECTS From Different Tables

Question

I am attempting to Insert rows with data from 2 tables. The first table is my users AspNetUsers and the second table AspNetUserRoles. I want to give all users the same role. I have tried the following:

  INSERT INTO [MyDB].[dbo].[AspNetUserRoles] ([UserId], [RoleId])
  SELECT (SELECT Id FROM [MyDB].[dbo].AspNetUsers) AS UserId,
 (SELECT Id FROM [MyDB].[dbo].[AspNetRoles] WHERE Name = 'Intermediary') AS RoleId

I get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

This is because the query is selecting all the users, which I want, but my SQL needs to be modified to insert each user and the same RoleId.

I think I might need to use a cursor, or how should I go about this? I am using MS SQL Server.

Answer 1

This should work:

INSERT INTO [MyDB].[dbo].[AspNetUserRoles] 
    ([UserId], [RoleId])
SELECT 
    Id,
    (SELECT Id FROM [MyDB].[dbo].[AspNetRoles] WHERE Name = 'Intermediary') AS RoleId
FROM
    [MyDB].[dbo].AspNetUsers

as long as WHERE Name = 'Intermediary' return 1 row.