BulkMerge with custom "update" section

[mmbilinski]

Hi there,

I would like to do a bulk insert of a few collections from different sources. Target DB table contains ID (let's assume it's string) an int field, which I would like to treat like an enum with flags, meaning the sources the value came from. So, after bulk merging these objects (in two separate BulkMerges):

("A", 1) ("A", 4)

I would like to have the DB row ("A", 5). For now, only the last value (4) is preserved. Can this be achieved now?

[JonathanMagnan]

Hello @mmbilinski ,

I'm not sure to really understand your request, could you try again?

From what I understand is that your source has 2 rows and you would like to sum the values? It could be possible by using some formula on your column as long as they are from different BulkMerge

Let me know more to make sure we understand correctly your scenario

Best Regards,

Jon

[mmbilinski]

Thanks for the reply, @JonathanMagnan. Yes, that's the case - same primary key in the database, different values that needs to be "added" to those already present in the db. In fact I need logical OR to be applied on those values, so ("A", 1), ("A", 4) and ("A", 4) again within 3 BulkMerges should still give ("A", 5).

[JonathanMagnan]

I talked about this scenario with my developer,

We believe it starts to be out of scope with your logical OR.

Is there a reason why you don't do this custom logic in your code instead and make the final BulkMerge within our library?

[JonathanMagnan]

Hello @mmbilinski ,

Since our last conversation, we haven't heard from you.

As mentioned in my previous message, We believe it starts to be out of scope with your logical OR.

Don't hesitate to provide further information so that we can help you better.

Looking forward to hearing from you,

Jon

[mmbilinski]

Hi @JonathanMagnan, sorry for delayed response, I had to switch context for a while.

I believe the logical OR is not the main problem. Please, take a look at the generated SQL code:

-- Executing Command:
MERGE INTO [SymbolTypes]  AS DestinationTable
USING
(
SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER ( PARTITION BY [Symbol] ORDER BY ZZZ_Index DESC ) AS ZZZ_Row_Number FROM 
(SELECT @0_0 AS [Symbol], @0_1 AS [Type], @0_2 AS ZZZ_Index
UNION ALL SELECT @1_0 AS [Symbol], @1_1 AS [Type], @1_2 AS ZZZ_Index
UNION ALL SELECT @2_0 AS [Symbol], @2_1 AS [Type], @2_2 AS ZZZ_Index
UNION ALL SELECT @3_0 AS [Symbol], @3_1 AS [Type], @3_2 AS ZZZ_Index)  AS StagingTable  ORDER BY ZZZ_Index
) AS StagingTable ORDER BY ZZZ_Index
) AS StagingTable
ON DestinationTable.[Symbol] = StagingTable.[Symbol]
WHEN MATCHED AND ZZZ_Row_Number = 1   THEN
UPDATE
SET     [Type] = StagingTable.[Type] -- HERE
WHEN NOT MATCHED AND (ZZZ_Row_Number = 1 OR (
StagingTable.[Symbol] IS NULL
))
 THEN
INSERT ( [Symbol], [Type] )
VALUES ( [Symbol], [Type] )
;

I would just like to apply some custom function on line marked as "HERE".

[JonathanMagnan]

Hello @mmbilinski ,

There is currently no easy way to do it, but you can add pretty much any valid SQL this way:

context.BulkMerge(list, options => options.PostConfiguration = bulk => {
    bulk.ColumnMappings.Single(x => x.SourceName == "Type").FormulaUpdate = @"StagingTable.[Type] + [AnyValidSqlStatement]";
});

Could this work for you?

[JonathanMagnan]

Hello @mmbilinski ,

Since our last conversation, we haven't heard from you.

Did you get the chance to try the provided solution?

Don't hesitate to contact us for further assistance,

Jon

[mmbilinski]

That's what I was looking for :) Thank you very much for your kind support!