Rajeev Pentyala – Microsoft Power Platform

Other day while executing SQL ‘MERGE’ statement on 2 tables, I ran in to following error.

The insert column list used in the MERGE statement cannot contain multi-part identifiers

Before we understand the reason and fix for this error, lets get the basics of ‘MERGE’ statement.

‘MERGE’ statement:

• ‘MERGE’ statement can be used, if you have 2 SQL tables and to synchronize both two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
• ‘MERGE’ runs insert, update, or delete operations on a target table from the results of a join with a source table.

Step by Step using ‘MERGE’:

Lets understand how MERGE works with an example.

• Create 2 tables, ‘Cust_Source’ and ‘Cust_Trg’. Column ‘CID’ acts as joining column between 2 tables.

• Lets use MERGE and sync ‘Cust_Trg’ using ‘Cust_Source’ table by covering following points:
  • Update the matching records in ‘Cust_Trg’ using the ‘Cust_Source’ rows.
  • Create (i.e., INSERT) the unavailable records in ‘Cust_Trg’ from ‘Cust_Source’.
  • Delete the excessive records from ‘Cust_Trg’ by comparing with ‘Cust_Source’ rows.

Query:

MERGE [dbo].[Cust_Trg] AS Trg
USING [dbo].[Cust_Source] AS Src
ON Trg.CID=Src.CID
WHEN MATCHED — Update the Target record fields from Source values.
THEN UPDATE SET Trg.Salary=Src.Salary,Trg.WorkExp=Src.WorkExp
WHEN NOT MATCHED BY TARGET — INSERT the record in Target as its not part of Target yet.
THEN INSERT (CID, [Name], Salary, WorkExp)
VALUES (CID, [Name], Salary, WorkExp)
WHEN NOT MATCHED BY SOURCE — Delete the record from Target as its not part of Source.
THEN DELETE

OUTPUT $action, –INSERT/UPDATE/DELETE
DELETED.CID AS TargetCID,
DELETED.Name AS TargetName,
DELETED.Salary AS TargetSalary,
DELETED.WorkExp AS TargetWorkExp,
INSERTED.CID AS SourceCID,
INSERTED.Name AS SourceName,
INSERTED.Salary AS SourceSalary,
INSERTED.WorkExp AS SourceWorkExp;

SELECT @@ROWCOUNT ‘No ofAffected rows’

• Execute the query and you would get results as below.

Issue encountered with MERGE:

• When I ran my initial MERGE query, ran in to following exception.

• Issue was due to the usage of ‘Alias’ in INSERT statement (i.e., Trg.Salary, etc.)
• Remove ‘Alias’ and rerun the query should solve the issue.

Notes:

• MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it doesn’t exist, or updating a row if it matches.
• When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements.

🙂