The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.

Question

Hi,

I have the following code

SELECT @schemaname = schema_name from (SELECT schema_name FROM #schema WHERE ROWNUMBER = @Init) as var1
 SELECT @divisionname = divisionname from (SELECT case when schema_name = 'schemename' then 'WCH'
   when schema_name = 'schemename2' then 'MUSS'
   else schema_name end as divisionname FROM #schema WHERE ROWNUMBER = @Init) as var2
 SELECT @divisionkey = dd.divisionkey from (SELECT dd.divisionkey FROM [dbo].[DimDivision] dd 
 left join #temptable tt on dd.[Warehouse] = @divisionname ) as var3

SET @sqlCommand = ' 
MERGE' + @schemaname  + '.[DimSalesOffice] so USING #temptable tt
ON (so.sales_division = '''@divisionname +''' and so.[SalesOfficeCode] = tt.SALES_OFFICE)
WHEN MATCHED
    THEN 
 UPDATE
 SET 
   so.[SalesOfficeCode] = tt.SALES_OFFICE,
   so.[SalesOfficeName]  = tt.OFFICE_NAME,
      so.[PriceList] = tt.PriceList,
   so.[DivisionKey] = '''+ @divisionkey +'''
WHEN NOT MATCHED
    THEN 
 INSERT ([SalesOfficeCode],[SalesOfficeName],[PriceList],[DivisionKey])
 Values (tt.SALES_OFFICE, tt.OFFICE_NAME,tt.PriceList,'''+@divisionkey+''')
;'

However I am getting the following errors

Msg 10739, Level 15, State 1, Procedure UpdateSalesOffices, Line 61 [Batch Start Line 7]
The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.

Any help appreciated

Answer 1

The syntax error is because the target column names of the SET clause should not be prefixed with an alias. No qualification is needed since the target columns are implicitly columns in the target table.

Below is a corrected example that also includes parameters for @divisionkey and @divisionname instead of injecting the values with string concatenation. Note I just guessed at the parameter data types for the example; change to match your actual column data types.

SET @sqlCommand = ' 
 MERGE' + @schemaname  + '.[DimSalesOffice] so USING #temptable tt
 ON (so.sales_division = @divisionname and so.[SalesOfficeCode] = tt.SALES_OFFICE)
 WHEN MATCHED
     THEN 
  UPDATE
  SET 
    [SalesOfficeCode] = tt.SALES_OFFICE,
    [SalesOfficeName]  = tt.OFFICE_NAME,
    [PriceList] = tt.PriceList,
    [DivisionKey] = @divisionkey
 WHEN NOT MATCHED
     THEN 
  INSERT ([SalesOfficeCode],[SalesOfficeName],[PriceList],[DivisionKey])
  Values (tt.SALES_OFFICE, tt.OFFICE_NAME,tt.PriceList,@divisionkey)
 ;';

 EXEC sp_executesql 
     @sqlCommand
     , N'@divisionname varchar(30), @divisionkey varchar(30)'
     , @divisionname = @divisionname
     , @divisionkey = @divisionkey;

Answer 2

Hi，@Christopher Jack

Welcome to Microsoft T-SQL Q&A Forum!

The error message indicates that there is a syntax error around merge , you can try to write @divsionname as a parameter directly into the statement , and the syntax you use for merge here is very strange , we often use merge into table join table1 , please check , if Can't solve it , please provide us some test data , we will do some tests.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.