CODEWITHSUNDEEP
sqlsql-servert-sql
Neha Thakur
Neha Thakur

Reputation: 351

Insert all values from a table's column to another table's column in Sql

I have two tables Roles and Menus and there is a relation on MenuId between these two tables. Both tables has relationship column with the same name "MenuId".

Menus has Primary Key and Roles has Foreign Key columns correspondingly.

I want to insert MenuId from Menus table to Roles Table using a loop, the Roles tables should have all the MenuId(s) from Menus table.

Something like as shown in below image. RoleID 1 then all MenuID, then RoleId 2 and again all MenuID.

But I do not want insertion using triggers.

I have tried something like :-

DECLARE @counter int
SET @counter = 0
WHILE @counter < 65 BEGIN
UPDATE Roles SET MenuId = @counter + 1
SET @counter = @counter + 1
END

enter image description here

Upvotes: 0

Views: 154

Answers (3)

James Casey
James Casey

Reputation: 2507

The elephant in the room is doing things in a loop when working with SQL - performance is generally terrible and you almost never need to.

Not entirely clear on your db structure, but this should point you in the right direction:

create table Menu
(
    MenuId int 
);

create table Role 
(
    RoleId int
);

create table RoleMenu
(
    RoleId int,
    MenuId int
);

insert into Menu(MenuId) values(1),(2),(3);
insert into Role(RoleId) values(1),(2),(3),(4);

-- some data already in table
insert into RoleMenu(RoleId, MenuId) values(1,1), (1,2);

with cte as (
    select distinct RoleId, MenuId
    from Menu 
    cross join Role
)
insert into RoleMenu(RoleId, MenuId)
select cte.RoleId, cte.MenuId
from cte
left outer join RoleMenu rm on cte.MenuId = rm.MenuId and cte.RoleId = rm.RoleId 
where rm.RoleId is null -- don't insert where we already have data

select * from RoleMenu order by RoleId, MenuId

Upvotes: 0

Vikrant
Vikrant

Reputation: 5046

To update existing RoleIds, you can use your method slightly modified:

DECLARE @counter int =0
DECLARE @records int

SELECT @records = COUNT(MenuId) FROM Menus

WHILE @counter < @records 
BEGIN

    UPDATE Roles SET MenuId = @counter + 1
    WHERE RoleId = 1
    SET @counter = @counter + 1
END

After that, if you want to insert each MenuId against every RoleId:
you can use "INSERT INTO ... SELECT ..." statement as,

DECLARE @roles int = 0

WHILE @roles < 10   --assuming that you have **10 roles** to insert
BEGIN
    INSERT INTO Roles(MenuId, RoleId)
    SELECT MenuId, @roles --it's a roleId you want to insert
    FROM Menus m

    SET @roles = @roles + 1
END

Above query will insert all MenuId against every RoleId

Upvotes: 1

lakshmi tatavarty
lakshmi tatavarty

Reputation: 59

create a 3rd table with the required columns and execute the below query on that table

insert into table3 (select table1.r_id,table2.menu_id from table1 cross join table2);

Upvotes: 1

Related Questions