Archive

Posts Tagged ‘merge’

SQL SERVER | Merge Statement | The insert column cannot contain multi-part identifiers

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.
‘Source’ and ‘Target’ 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.
‘MERGE’ query
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.
Don’t use Alias in ‘INSERT’
  • 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.

🙂

Categories: SQL Tags: , ,

Merging solutions in CRM 2011

July 17, 2013 2 comments

Imagine you have 2 developers (Dev 1 & Dev 2) in your team working on 2 different organizations and 2 solutions, at the time of every release, you need to merge the components of both developers in to 1 solution.

One way is

  • Import Dev 1 & Dev 2 solutions in to another new organization.
  • Create a new solution and add the components of both solutions manually
  • This is easy if Dev 1 & Dev 2 solutions has less components.

What if the components are huge? you need to manually pick and add components both solutions in to a single solution or

Another way of merging solutions

  • Export & Save the both Dev 1 & Dev 2 solutions
  • Extract the saved solution .zip folders
  • Open “Solution.xml” files in both solutions in a notepad

Solution components

Solution components

  • In the <UniqueName> node, provide same unique name in both “Solution.xml” files

SolutionXml file

SolutionXml file

  • Import them back to new a organization
  • After the import, we get a new merged solution.
  • This is easier way of merging if the solutions has lot of components

Refer below MSDN video for more details

🙂

Categories: CRM 2011 Tags: , ,