how to loop through a list of ID's in SQL Server

Question

I have a list of ids that i need to loop through, and use in an insert statement for example

I have built the table here:

DECLARE @ProductIds TABLE (ID nvarchar(1000))

INSERT INTO @ProductIds (ID)
    SELECT ProductTypeId FROM ProductType

DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
    SELECT DISTINCT Id 
    FROM @ProductIds

OPEN MY_CURSOR

FETCH NEXT FROM MY_CURSOR INTO @ProductIds

WHILE @@FETCH_STATUS = 0
BEGIN 
    --each so basically each productId is used in the @ProductId parameter below
    INSERT INTO myProducts (ProductTypeId, prodDesc, Value) 
    VALUES (@ProductId, 'CA1BBFC3-35EA-4984-BCD1-9E0EB385E4BE', 0)

    PRINT @ProductIds

    FETCH NEXT FROM MY_CURSOR INTO @ProductIds
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

I'm not sure if the problem is the syntax or how I've tried to do it but I keep getting errors

Msg 137, Level 16, State 1, Line 76
Must declare the scalar variable "@ProductIds".

Msg 137, Level 16, State 1, Line 81
Must declare the scalar variable "@ProductIds".

Msg 137, Level 16, State 1, Line 82
Must declare the scalar variable "@ProductIds".

I'm learning SQL so I'm finding it quite a challenge at the moment

Stuck at 1337 · Accepted Answer · 2023-06-02 17:22:07Z

4

If you are learning SQL, the first thing you should do is stop thinking about looping anything. SQL is optimized to operate on sets, not one row at a time. You just need:

insert dbo.myProducts (ProductTypeId, prodDesc,Value) 
  Select ProductTypeId,'CA1BBFC3-35EA-4984-BCD1-9E0EB385E4BE',0 
  from dbo.ProductType;

Though this seems a little messed up; there is some confusion between a ProductId and a ProductTypeId, and that you want to insert one product with the same GUID description for every product type. But that's the general structure to avoid 70 lines of code and an inefficient loop.

edited Jun 2, 2023 at 17:22

answered Oct 16, 2022 at 16:16

Stuck at 1337

2,0941 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to loop through a list of ID's in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related