Cursor stuck in an infinite loop

First attempt at a cursor so take it easy =P The cursor is supposed to grab a list of company ids that are all under a umbrella group. Then target a specific company and copy its workflow records to the companies in the cursor.

It infinitely inserts these workflow records into all the companies ... what is the issue here?

Where is the n00b mistake?

DECLARE @GroupId int = 36;
DECLARE @CompanyToCopy int = 190
DECLARE @NextId int;
Declare @Companies CURSOR;

SET @Companies = CURSOR FOR 
SELECT CompanyId
FROM Company C 
    INNER JOIN [Group] G 
        ON C.GroupID = G.GroupID
WHERE   C.CompanyID != 190 
        AND
        G.GroupId = @GroupId
        AND
        C.CompanyID != 0

OPEN @Companies
FETCH NEXT
FROM @Companies INTO @NextId

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO COI.开发者_JAVA技巧Workflow(CompanyID, EndOfWorkflowAction, LetterType, Name)
    (SELECT 
            @NextId,
            W.EndOfWorkflowAction,
            W.LetterType,
            W.Name
     FROM COI.Workflow W)

    FETCH NEXT
    FROM @Companies INTO @NextId
END
CLOSE @Companies;
DEALLOCATE @Companies;

Edit:

I decided to attempt making this set based just because after being told to do it ... I realized I didn't really quite have the answer as to how to do it as a set based query.

Thanks for all the help everyone. I'll post the set based version for posterity.

INSERT INTO COI.Workflow(CompanyID, EndOfWorkflowAction, LetterType, Name)
(
SELECT 
    CG.CompanyId,
    W.EndOfWorkflowAction,
    W.LetterType,
    W.Name
FROM COI.Workflow W
     CROSS JOIN (SELECT C.CompanyID
                 FROM Company C 
                    INNER JOIN [Group] G
                        ON G.GroupID = C.GroupID
                 WHERE  C.CompanyID != 190 
                        AND 
                        C.CompanyID != 0
                        AND 
                        G.GroupID = 36
                 ) AS CG
 WHERE W.CompanyID = 190
 )

---

You have no WHERE condition on this:

SELECT 
            @NextId,
            W.EndOfWorkflowAction,
            W.LetterType,
            W.Name
     FROM COI.Workflow W
     -- WHERE CompanyID = @CompanyToCopy -- This should be here

So you are getting a kind of doubling effect.

initial state, company 190, seed row (0)

pass one, company 2, copy of seed row (1)
now 2 rows

pass two, company 3, copy of seed row (0) - call this (2)
pass two, company 3, copy of copy of seed row (1) - call this (3)
now 4 rows

then 8 rows, etc

---

You are inserting a new copy of all workflow records in the workflow table for each iteration, so it will double in size each time. If you for example have 30 items in your cursor, you will end up with a workflow table with 1073741824 times more records than it had before.

---

I beieve your logic is wrong (it's somewhat hidden because of the use of a cursor!).

Your posted code is attempting to insert a row into into COI.Workflow for every row in COI.Workflow times the number of companies matching your first select's conditions. (Notice how your insert's SELECT statement has no condition: you are selecting the whole table). On each time through the loop, you are doubling the number of rows in COI.Workflow

So, it's not infinite but it could well be very, very long!

I suggest you rewrite as a set based statement and the logic will become clearer.

---

First use of cursor is OK, all problems in INSERT ... SELECT logic. I cannot understand what do you need to insert into COI.Workflow table. I agree with previous commentatorts that your current WHERE condition doubles records, but I cannot believe that you want to insert the full-doubled records for each company each time. so, I think you need something like

INSERT INTO COI.Workflow(CompanyID, EndOfWorkflowAction, LetterType, Name)
(SELECT TOP 1
        @NextId,
        W.EndOfWorkflowAction,
        W.LetterType,
        W.Name
 FROM COI.Workflow W)

Or, we need to know more about your logic of inserting the records.