CODEWITHSUNDEEP
sql-server
Aiden
Aiden

Reputation: 313

SSMS Alter Table Row not being accessed anywhere else

I'm creating a temp table to store data from a csv and then altering the table after to create a new column to identify each row by a unique number in ascending order.

This gets created fine, however I can't query the table using these row numbers. Seems as if it doesn't get set. On SSMS when I use the newly created column it red lines it with the error Invalid Column Name 'columnName' but I can still query the database.

declare @loopNum INT
set @loopNum = 0

    CREATE TABLE #A
    (
        column1 BIGINT NOT NULL,
        column2 BIGINT NOT NULL,
    )

    DECLARE @command NVARCHAR(150)
    SET @command = just reads from file into temp table A. this works fine
    EXEC SP_EXECUTESQL @command

    ALTER TABLE #A
    ADD RowNumbers INT IDENTITY(1,1)
 --if i run a select * from #a, all 4 columns show perfectly
while @loopNum <= 5
begin
select * from #a where loopNum = RowNumbers -- doesn't return anything yet loop is going up one as 6 blank results are returned
set @loopNum = @loopNum + 1   
end

The select statement doesn't recognise "RowNumbers" so I'm not sure if there's a problem with how I've done the alter command.

This is what I get so far.

Column 1 | Column 2 | RowNumbers
A        | B        | 1
C        | D        | 2

It just doesn't loop through it.

Upvotes: 1

Views: 31

Answers (1)

Jacob H
Jacob H

Reputation: 2505

A couple of issues here.

One, you're going to get an empty row first always because you declared @loopNum = 0 and your WHILE loop starts at 1.

Two, you are using "loopNum" instead of your variable @loopNum in your SELECT.

Rextester: http://rextester.com/XJNML62761

Upvotes: 1

Related Questions