question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked ChristopherJack-1763 commented

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

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


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
1 Vote"
DanGuzman answered ChristopherJack-1763 commented

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;


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Dan,

I have added your code in

     SET @sqlCommand = ' 
   MERGE' + @schemaname  + '.[DimSalesOffice] so USING #temptable tt
   ON 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(20), @divisionkey varchar(20)'
       , @divisionname = @divisionname
       , @divisionkey = @divisionkey;

However I am still receiving the following errors

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


Thanks

Chris


0 Votes 0 ·
TomPhillips-1744 avatar image TomPhillips-1744 ChristopherJack-1763 ·

You are missing a ( here

  ON so.[SalesOfficeCode] = tt.SALES_OFFICE)
1 Vote 1 ·
TomPhillips-1744 avatar image TomPhillips-1744 ChristopherJack-1763 ·

Please PRINT @sqlcommand and post the results.

0 Votes 0 ·

Hi Tom,

Thanks that helped me to identify the issue.
It turns out the issue was related to a previous merge statement used further up the code!

Thanks for everyones help.

Chris

0 Votes 0 ·
BertZhoumsft-7490 avatar image
1 Vote"
BertZhoumsft-7490 answered NaomiNNN commented

Hi,@ChristopherJack-1763

Welcome to Microsoft T-SQL Q&A Forum!

195066-image.png
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.



image.png (7.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.