title: Nested cursor in SQL Server
body: <p>I have three different tables defined as follows: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>INSERT [dbo].[access_switch] ([item_id], [role_id], [status])
  VALUES (@item_id, @role_id, 'OFF')
  </code></pre>
  
  <p>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).</p>
  
  <p>My script so far looks like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>As you can see, I am trying to loop through the ID's in the <code>access_role</code> table first, and then inside that loop I want to go through the ID's in the <code>access_item</code>. However, the script does not produce any data after I run it. Does anyone have any suggestions?</p>
  
  <p>EDIT:
  A sample insert statements for access_item:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>Sample insert statements for access_role:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>My desired output would ideally correspond to thisinsert statements:</p>
  
  <pre><code>INSERT INTO access_switch (item_id, role_id, status) VALUES (2, 1, 'OFF')
  </code></pre>
  
  <p>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.</p>