4

I am trying to UPDATE a specific column within a table, but I get an error due to the fact that when SQL compiles, the column name IssueTimeUTC does not actually exist. Here is my sample code:

   WHILE  @startissuetime<='23:30'
        BEGIN
        ALTER TABLE Intraday_Forecast_temp
            ADD IssueTimeUTC SMALLDATETIME 

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
            WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
            WHERE

DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME) END

I have read multiple similar posts, but I cannot make anything work therefore I am kindly asking you if you could help me.

The error I get is this:

Msg 207, Level 16, State 1, Line 56 Invalid column name 'IssueTimeUTC'.

Update: So basically I was not able to find an exact solution to this specific problem but I just found a way to go ''around'' the problem instead. So this is the updated code I used.

WHILE  @startissuetime<='23:30'
    BEGIN
        ALTER TABLE Intraday_Forecast_temp
        DROP COLUMN IssueTimeUTC 
        --------
        BULK INSERT..... 
        --------
        ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME 

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
    END

I know that this is not probably the most elegant solution but I initially defined the Intraday_Forecast_temp table WITH the column IssueTimeUTC , then I drop it and add it again. This way, SQL stop complaining that the column does not exist upon compilation :)

  • 5
    Putng the ALTER TABLE .. ADD COLUMN inside a loop does not seem like a good idea ;) – ypercubeᵀᴹ Dec 14 '17 at 10:27
  • 1
    Plus, I don't see how the WHILE loop is going to end. Do you want it to run forever? – ypercubeᵀᴹ Dec 14 '17 at 10:29
  • Can you give a little more detail as to what exactly you're trying to achieve?Like, why are you trying to add a column multiple times? What is your current state, and what is your desired state (for the data)? – Nestor Dec 14 '17 at 15:17
  • I am ending the WHILE loop by using an IF BREAK statement! – Nisfa Dec 15 '17 at 13:23
6

You cannot add the column and use it in the same batch.

So if you execute your code in SSMS just add GO between your commands like this:

    ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME 

go ----------------------------<<<<<-----------------------------

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)

If it's a part of stored procedure, wrap your update in EXEC:

exec ('UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)')

UPDATE

In your case you can use wrapping in exec this way:

declare @tempdate date = getdate(); -- or whatever it should be
declare @sql nvarchar(4000)= N'UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)';

exec sp_executesql @sql, N'@tempdate date', @tempdate = @tempdate;
|improve this answer|||||
  • I have tried that but it actually does not work, because my ALTER and UPDATE statements are within a WHILE loop. So if I add a GO in between, then I get an error that my variable @tempdate has not been declared – Nisfa Dec 14 '17 at 10:02
  • Please update your question with the code you want to execute. Of course the visibility of a variable is batch only,maybe you can genetare your values using sequence or fill the table with these values and then access them from your loop – sepupic Dec 14 '17 at 10:07
0

Just do this.

Execute first the ALTER statement

ALTER TABLE Intraday_Forecast_temp
    ADD IssueTimeUTC SMALLDATETIME

Have it executed successfully then execute the UPDATE statement.

UPDATE Intraday_Forecast_temp
SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

UPDATE Intraday_Forecast_temp
SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.