CODEWITHSUNDEEP
mysqlsql-server
Spider Man
Spider Man

Reputation: 425

Insert into a table values as the result of a SQL query

I have three tables Users, Roles and UserRoles. Users has a primary key called as Id, Roles has a primary key called Id and UserRoles has two foreign key references to Users and Roles called UserId and RoleId.

I need to insert into the UserRoles a UserId, RoleId pair as the result of a SQL query on the Users and Roles table.

The queries are something like this:

SELECT ID FROM Users WHERE Email = '[email protected]';

and

SELECT ID From Roles WHERE Name = 'Admin';

I need to use them like this:

INSERT INTO UserRoles (UserId, RoleId) VALUES (SELECT ID FROM Users WHERE Email = '[email protected]', SELECT ID From Roles WHERE Name = 'Admin');

How can I do this.

Upvotes: 0

Views: 823

Answers (2)

Manish
Manish

Reputation: 3663

Try this.

INSERT INTO UserRoles (UserId, RoleId) 
     SELECT a.ID, b.ID 
     FROM Users as a,Roles as b 
     WHERE a.Email='[email protected]'
     AND b.Name='Admin';

Upvotes: 1

Abhinav
Abhinav

Reputation: 1200

this should work for you..

  declare @userId int, @roleid int
    select @userId = Id FROM Users 
    select @roleid = Id from Roles
    insert into UserRoles (UserId, RoleId) value (@userId,@roleid)

Please consider your where condition into select queries.

Upvotes: 1

Related Questions