Q:

“Invalid column name” error in SQL Server 2014 when creating a merge procedure

I'm trying to create a merge procedure using multiple columns. However, I'm also getting multiple errors about column names.

I would appreciate pointers on where I'm going wrong.

create PROCEDURE [Etl].[MergeRPIData2] AS

SET NOCOUNT ON

MERGE [RetailPrice].[RetailPriceIndex] AS Trgt
USING (SELECT ItemId, Outlet, DataYear, DataMonth
    FROM [RetailPrice].[StagingRPI]) AS Src (ItemId, Outlet, DataYear, DataMonth)
    ON Trgt.ItemId=Src.ItemId AND Trgt.Outlet=Src.Outlet AND Trgt.DataYear=Src.DataYear AND Trgt.DataMonth=Src.DataMonth

WHEN MATCHED THEN 
    UPDATE SET ID=Src.ID, ItemId=Src.ItemId, Item=Src.Item, RPIType=Src.RPIType, Outlet=Src.Outlet, RPIWeight=Src.RPIWeight, PrevMonth=Src.PrevMonth, 
            CurrMonth=Src.CurrMonth, Remarks=Src.Remarks, DataYear=Src.DataYear, DataMonth=Src.DataMonth

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, ItemId, Item, RPIType, Outlet, RPIWeight, PrevMonth, CurrMonth, Remarks, DataYear, DataMonth)
    VALUES (Src.[ID], Src.[ItemId], Src.[Item], Src.[RPIType], Src.[Outlet], Src.[RPIWeight], Src.[PrevMonth], Src.[CurrMonth], Src.[Remarks], Src.[DataYear], Src.[DataMonth])

WHEN NOT MATCHED BY SOURCE THEN 
    DELETE;

DELETE FROM [RetailPrice].[StagingRPI]

A:

Reading SQL on a picture is not easy... However one thing that stroke my eye is that your WHEN MATCHED and WHEN ǸOT MATCHED clauses refer to many columns in Src that are not actually returned by the Src subquery.
Namely, Src returns 4 columns :

ItemId
Outlet
DateYear
DataMonth

Meanwhile, the WHEN (NOT) MATCH clauses refer to following fields (missing fields are indicated as bold) :

ID
ItemId 
Item 
RPIType
Outlet
RPIWeight
PrevMonth
CurMonth
Remarks
DataYear
DataMonth

I imagine that all missing fields are actually available in the [RetailPrice].[StagingRPI] tables, that Src is based upon (if not, you have a serious design problem).
To solve the multiple Invalid column name errors that the query is generating, you need to adapt the Src subquery so it returns all needed fields. I could not generate the correct query for you as you did not post the original query as text.

