Request: MergeRequest support

[clintonbale]

Hoping support for MergeRequest can be added, so the SQL4CDS tool can initiate bulk merges based on conditions.

Syntax perhaps looking something like this:

DECLARE @contact AS EntityReference = CREATELOOKUP('contact', '{myid}');
DECLARE @contact_dupe AS EntityReference = CREATELOOKUP('contact', '{myid}');

EXECUTE [dbo].[Merge]
    @Target = @contact, -- or using CREATELOOKUP(...)
    @SubordinateId = @contact_dupe, 
    @PerformParentingChecks = 1,
    @UpdateContent = ('emailaddress1', 'firstname')

Understanding that @Target is of type Entity in the SDK may also prove troublesome, but it seems that it may be irrelevant to the merge process and is only used as an EntityReference behind the scenes

The @UpdateContent parameter here is likely the trickiest and may require some custom syntax. The syntax in the above example would copy the values in the listed fields from the subordinate to the target. Syntax like the below example could allow for more dynamic values

DECLARE @lastName VARCHAR(100) = 'Smith'

EXECUTE [dbo].[Merge]
    -- ...
    @SubordinateId = @contact_dupe as src, 
    @UpdateContent = ('emailaddress1', 'firstname', 'lastname') values ('[email protected]', [src].[firstname], @lastName)

---

• Deployment: Online
• DB Version: 9.2.23093.196
• Connection Controls Version: 1.2023.6.56
• XrmToolBox Version: 1.2023.9.66
• Tool Version: 7.6.0.0

[MarkMpn]

The Target parameter is an EntityReference type, so that's no problem. As you say, the difficulty here is the UpdateContent parameter.

Custom syntax is not an option - SQL 4 CDS uses the standard T-SQL parser, so any syntax must be compatible with that. The standard SQL Server way of passing a data record into a stored procedure is to declare a custom table type variable, so the syntax would be more like:

DECLARE @UpdateContent TABLE (
  emailaddress1 nvarchar(100),
  firstname nvarchar(100),
  lastname nvarchar(100)
)

INSERT INTO @UpdateContent VALUES ('New email address', 'New firstname', 'New lastname')

EXEC [Merge]
  @Target = @targetReference,
  @SubordinateId = @subordinateGuid,
  @UpdateContent = @UpdateContent

Supporting in-memory custom tables like this is likely to be a lot of work and not something I'm planning on implementing in the near future.