Need advice on solving an Invalid column name error

Question

I am a SQL newbie trying to troubleshoot code left by my predecessor. When I say newbie, it mean that I have completed a couple of introductory level online courses.

I am using SSMS connected to an employers server. Below is a portion of the offending code. As I understand it, the code points to a database called Revenue DM copy. Within this database is a table called GPT Dashboard. The table GPT Dashboard is populated by data from a table called GPT Dashboard SFDC via the command "Select * into [dbo].[GPT Dashboard] from [dbo].[GPT Dashboard SFDC]. I have verified that this update is correctly working meaning all the data are getting transferred. The next line of code alters the GPT Dashboard table by adding 7 new columns starting with [UPH1] and ending with [Product Name].

The next section is where the code tips over. Table GPT Dashboard gets altered by setting the new aforementioned 7 columns equal to values contained in a third table called Product UPH mapping. I have verified that these 7 columns do in fact exist within Product UPH mapping. All seven of the new columns added to GPT Dashboard have the red squiggly line under them, see the below code. These errors prevent the code from executing.

This code is acting like the Alter Table and subsequent Add commands are not executing. I have tried inserting GO statements and running the code in small batches and in out of order sequences but with no luck. Any advice is much appreciated but please keep responses very simple...like talking to a 5 year old simple. Yes, I am that green!

Thanks!

USE [Revenue DM Copy]
GO
/****** Object:  StoredProcedure [dbo].[SP_02_GPT_Dashboard]    Script Date: 7/23/2020 1:27:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE OR ALTER PROCEDURE dbo.SP_02_GPT_Dashboard
    -- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON
-- Insert statements for procedure here
-- Input Tables
-- select count(*) from [dbo].[GPT Dashboard SFDC]
-- select * from [dbo].[Product UPH mapping]

IF OBJECT_ID('[dbo].[GPT Dashboard]', 'U') IS NOT NULL 
drop table [dbo].[GPT Dashboard]
select * into [dbo].[GPT Dashboard] from [dbo].[GPT Dashboard SFDC]

Alter table [dbo].[GPT Dashboard]
add [UPH1] varchar(100),
    [UPH2] varchar(100),
    [UPH3] varchar(100),
    [UPH4] varchar(100),
    [UPH5] varchar(100),
    [UPH6] varchar(100),
    [Product Name] varchar(500)
update [dbo].[GPT Dashboard]
set [dbo].[GPT Dashboard].[UPH1] = [dbo].[Product UPH mapping].[UPH1], --Red line under first instance of UPH1 right after [GPT Dashboard].
    [dbo].[GPT Dashboard].[UPH2] = [dbo].[Product UPH mapping].[UPH2], --Red line under first instance of UPH2 right after [GPT Dashboard].
    [dbo].[GPT Dashboard].[UPH3] = [dbo].[Product UPH mapping].[UPH3], --Red line under first instance of UPH3 right after [GPT Dashboard].
    [dbo].[GPT Dashboard].[UPH4] = [dbo].[Product UPH mapping].[UPH4], --Red line under first instance of UPH4 right after [GPT Dashboard].
    [dbo].[GPT Dashboard].[UPH5] = [dbo].[Product UPH mapping].[UPH5], --Red line under first instance of UPH5 right after [GPT Dashboard].
    [dbo].[GPT Dashboard].[UPH6] = [dbo].[Product UPH mapping].[UPH6], --Red line under first instance of UPH6 right after [GPT Dashboard].
    [dbo].[GPT Dashboard].[Product Name] = [dbo].[Product UPH mapping].[Product_Name] --Red line under first instance of product name right after [GPT Dashboard].
from
    [dbo].[GPT Dashboard]
inner join
    [dbo].[Product UPH mapping]
on
    [dbo].[GPT Dashboard].[Quote_Product_Part_Number] = [dbo].[Product UPH mapping].[Oracle_CPQ_Cloud_Part_Number]

Answer 1

The update will be parsed first for validity checks, and it will find that those columns don't exist yet. The parser doesn't have the ability to read all of the code and determine what will be the end state at the time the update runs; it can only rely on what it knows from the SELECT INTO. (Interestingly, it works fine if you define the table first using CREATE TABLE.)

So instead of:

SELECT * INTO dbo.foo FROM ...;
ALTER TABLE dbo.foo ADD bar int;
UPDATE dbo.foo SET bar = 5;

You will have to reference any not-present-at-create-time columns using dynamic SQL:

SELECT * INTO dbo.foo FROM ...;
ALTER TABLE dbo.foo ADD bar int;
EXEC sys.sp_executesql N'UPDATE dbo.foo SET bar = @bar;', N'@bar int', 5;

In SSMS, you'd get around this by separating the statements into separate batches, e.g.

SELECT * INTO dbo.foo FROM ...;
GO
ALTER TABLE dbo.foo ADD bar int;
GO
UPDATE dbo.foo SET bar = 5;

But GO is not T-SQL, it's a batch separator in SSMS, and you can't use it inside a stored procedure.