CODEWITHSUNDEEP
sqlsql-serverdatabase
user12955946
user12955946

Reputation:

Nested cursor in SQL Server

I have three different tables defined as follows: 

CREATE TABLE access_item 
(
    item_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    item_name VARCHAR(50) NOT NULL,
)
GO

CREATE TABLE access_role 
(
    role_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    role_name VARCHAR(75) NOT NULL,
    status VARCHAR(120) NOT NULL
)
GO

CREATE TABLE access_switch 
(
    switch_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    item_id INT NOT NULL,
    role_id INT NOT NULL,
    access_level VARCHAR(10) NOT NULL,

    FOREIGN KEY (item_id) REFERENCES access_item(item_id),
    FOREIGN KEY (role_id) REFERENCES access_role(role_id)
)
GO

I want to generate seed data for my database. This data will be inserted into the access_switch table. A sample insert for this table would look like:

INSERT [dbo].[access_switch] ([item_id], [role_id], [status])
VALUES (@item_id, @role_id, 'OFF')

Manually doing the inserts would lead to around 52 insert queries. I want to write a script to do this for me instead. I attempted to use cursors for this (I have read that speeds can take a hit through this, but the amount of data I'm working with is minimal).

My script so far looks like:

DECLARE
    @role_id INT,
    @item_id INT;

DECLARE @roleTable CURSOR FOR 
    SELECT [role_id]
    FROM [esg_scoring_db.dev].[dbo].[access_role]

OPEN @roleTable

FETCH NEXT FROM @roleTable INTO @role_id

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @itemTable = CURSOR FOR 
                         SELECT [item_id]
                         FROM [esg_scoring_db.dev].[dbo].[access_item]

    OPEN @itemTable

    FETCH NEXT FROM @itemTable INTO @item_id

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT [dbo].[access_switch] ([item_id], [role_id], [access_level], [created_by])
        VALUES (@item_id, @role_id, 'OFF', 'system')

        FETCH NEXT FROM @itemTable INTO @item_id
    END

    CLOSE @itemTable
    DEALLOCATE @itemTable

    FETCH NEXT FROM @roleTable INTO @role_id
END

CLOSE @roleTable
DEALLOCATE @roleTable
GO

As you can see, I am trying to loop through the ID's in the access_role table first, and then inside that loop I want to go through the ID's in the access_item. However, the script does not produce any data after I run it. Does anyone have any suggestions?

EDIT: A sample insert statements for access_item:

INSERT INTO access_item (item_name) VALUES ('Record1')
INSERT INTO access_item (item_name) VALUES ('Record2')
INSERT INTO access_item (item_name) VALUES ('Record3')
INSERT INTO access_item (item_name) VALUES ('Record4')

Sample insert statements for access_role:

INSERT INTO access_role (role_name, status) VALUES ('Name1', 'OFF')
INSERT INTO access_role (role_name, status) VALUES ('Name2', 'OFF')
INSERT INTO access_role (role_name, status) VALUES ('Name3', 'OFF')
INSERT INTO access_role (role_name, status) VALUES ('Name4', 'OFF')

My desired output would ideally correspond to thisinsert statements:

INSERT INTO access_switch (item_id, role_id, status) VALUES (2, 1, 'OFF')

However, I want to create records in the access_switch table where each row is referencing a unique combination of item_id and role_id.

Upvotes: 2

Views: 619

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

You DO NOT need cursors here at all. You are simply creating a cartesian product of the two tables. You can accomplish painlessly with a cross join. This should generate the 16 rows you are looking for.

insert access_switch
(
    item_id
    , role_id
    , access_level
)
select i.item_id
    , r.role_id
    , r.status
from access_item i
cross join access_role r

Upvotes: 2

Related Questions