How to FETCH from a cursor into a table variable?

Question

I have a cursor defined as:

DECLARE idCursor CURSOR         
    FOR SELECT DISTINCT [id], [data]
        FROM #tempTable

And I have a variable defined as:

DECLARE @currentId TABLE (
                            [id] int,
                            [data] char(1)
                         );

But when I try to fetch the next result into @currentId

FETCH NEXT FROM idCursor INTO @currentId

I get this error:

Must declare the scalar variable "@currentId".

How can I get the result of a cursor fetch in a table? I understand that I can declare two variables and store the results individually. But for a table with many columns that would be time consuming.

Aaron Bertrand · Accepted Answer · 2018-04-23T16:40:18.853

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert:

FETCH NEXT FROM idCursor INTO @id, @data;
INSERT @currentId([id],[data]) SELECT @id, @data;

However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why not populate the @table variable with whatever query originally populated #tempRemaining?

INSERT @currentId([id], [data])
SELECT DISTINCT [id], [data]
    FROM #tempTable;

Or even just using #tempTable by itself and skipping the table variable altogether? Maybe even skipping the #temp table? A cursor combined with a table variable and a #temp table just sounds like a nightmare.

At the risk of sounding like Celko, this seems very much like flat file processing from the 1970s... and it's even worse that there are so many columns that declaring those variables would be prohibitive.

score 0 · Answer 2 · answered Jan 30 '18 at 15:44

Simple example using cursor:

DECLARE @CustomerID as INT;
declare @msg varchar(max)
DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT CustomerID FROM Customer WHERE CustomerID IN ('3908745','3911122','3911128','3911421')

OPEN @BusinessCursor;
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msg = '{
              "CustomerID": "'+CONVERT(varchar(10), @CustomerID)+'",
              "Customer": {
                "LastName": "LastName-'+CONVERT(varchar(10), @CustomerID) +'",
                "FirstName": "FirstName-'+CONVERT(varchar(10), @CustomerID)+'",    
              }
            }|'
        print @msg
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
END

How to FETCH from a cursor into a table variable?

2 Answers2