2

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.

Improve this question
4
  • 3
    My first suggestion would be to start thinking in sets instead of RBAR (row by agonizing row). For the inserts here you need exactly one insert statement. Nothing else, no cursors, no looping. Great job posting table definitions. Some sample data and desired output would help here. If you don't have any rows in access_item the looping your wrote will not do anything because there is not data for it to find. Commented Feb 24, 2020 at 20:17
  • @SeanLange I have the correct data in access_item and access_role. I was initially creating this seed data manually which worked, I'm just trying to write a script to do it for me now. Thanks! Commented Feb 24, 2020 at 20:26
  • Not sure I follow what you want inserted in to access_switch. Are you expecting 16 rows in that table from the sample data you provided? Commented Feb 24, 2020 at 20:30
  • Yeah, I am expecting 16 rows. I believe my insert statements for access_role and access_item might be a little misleading, as I don't explicitly show the role_id and item_id fields. This is because I have a default value set for them using the IDENTITY tag in my create_table Commented Feb 24, 2020 at 20:33

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.