Error message while using Select Into

Question

I used below code,

SELECT RowsCount = ROW_NUMBER() OVER(ORDER BY PrijsBoek.[Item Code] DESC, PrijsBoek.[Buy-From BP Code] DESC),
*
INTO #Student
FROM [iqbs DataWarehouse].[Purchase].[factPurchasePriceBooks] as PrijsBoek

DECLARE @MaxRowsCount INT
SET @MaxRowsCount = (SELECT MAX(RowsCount) FROM #Student)
DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowsCount) FROM #Student)
WHILE @Iter <= @MaxRowsCount
BEGIN
    SELECT *
    FROM #Student
    WHERE RowsCount = @Iter
    SET @Iter = @Iter + 1
END
DROP TABLE #Student

But I get the following error message(s):

Msg 207, Level 16, State 1, Line 7
Invalid column name 'RowsCount'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'RowsCount'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'RowsCount'.

Anybody any clue?

Is this code acually an exact copy of the code or have you manually re-written any of it? In my mind you should not get this issue so I'm thinking that you might have misspelled "Rowscount" in the first case in your real code. — Erik Blomgren, Aug 11 '16 at 07:28

score 0 · Answer 1 · answered Aug 11 '16 at 12:39

Try following code and adopt it to your situation:

SELECT RowsCount = ROW_NUMBER() OVER(ORDER BY Message_Id DESC), * 
INTO #Student
FROM sys.Messages

DECLARE @MaxRowsCount INT = @@ROWCOUNT, @Iter INT = 1, @Text AS VARCHAR(MAX);
SELECT @Iter , @MaxRowsCount

WHILE @Iter <= @MaxRowsCount
BEGIN
    SELECT @Text = Text FROM #Student WHERE RowsCount = @Iter
    PRINT @Text
    SET @Iter += 1;
END
DROP TABLE #Student

Error message while using Select Into

1 Answers1