Getting individual count of insert and update using merge statement - C# and SQL Server

Question

I am using MERGE command to upsert rows (using dapper ORM), and I would like to get the count of individual insert and update separately. I have dumped the merge actions to a temp table to query later. The rows are getting correctly upserted when I check against the database, however I'm not able to get the count of each action

Here I have added a list containing 3 items via C#, and the merge statement correctly adds them.

On inspecting the result that stores the operation (insert or update) I see count as 1 instead of 3

The temp table used in the merge statement when executed in SSMS using a similar source and destination table returns the table with correct count:

Please let me know if I am doing something wrong. Thank you.

I'll paste the C# code for reference:

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int Rate { get; set; }
}

    public async Task<IEnumerable<Tuple<string, int>>> UpsertProductCostAsync()
    {
        var products = new List<Product> 
                    {
                        new Product {ProductID = 1, ProductName = "Tea", Rate = 10},
                        new Product {ProductID = 2, ProductName = "Coffee", Rate = 20},
                        new Product {ProductID = 3, ProductName = "Muffin", Rate = 30}
                    };

        var mergeStatement = @"DROP TABLE IF EXISTS ##MergeActions
                               CREATE TABLE ##MergeActions ([MergeAction] VARCHAR(10))
                               INSERT INTO ##MergeActions ([MergeAction])
                                   SELECT [MergeAction]
                                   FROM
                                       (MERGE [dbo].[Products] AS target 
                                        USING (VALUES (@ProductID, @ProductName, @Rate)) AS source (ProductID, ProductName, Rate) 
                                              ON (target.ProductID = source.ProductID)
                                        WHEN MATCHED THEN
                                            UPDATE
                                                SET target.ProductName = source.ProductName,
                                                    target.Rate = source.Rate
                                        WHEN NOT MATCHED THEN
                                            INSERT ([ProductID],[ProductName], [Rate]) 
                                            VALUES (source.ProductID, source.ProductName, source.Rate)
                                        OUTPUT $action AS MergeAction) MergeOutput;
                                        ";

        _con.Open();
        var rowsAffected = await _con.ExecuteAsync(mergeStatement, products);
        var operationsWithCount = await _con.QueryAsync(@"SELECT [MergeAction], COUNT(*) as [Count]
FROM ##MergeActions  
GROUP BY MergeAction;");

        var result = new List<Tuple<string, int>>();

        foreach (var o in operationsWithCount)
        {
            result.Add(new Tuple<string, int>(o.MergeAction, o.Count));
        }

        return result;
    }

SQL Server table definition:

CREATE TABLE Products
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate int
)

Answer 1

I'd suggest starting with a table-value parameter in SQL Server. That'll let you pass in your list of products and get the result in the same SQL command.

CREATE TYPE TVP_Product AS TABLE
(
    ProductID integer not null,
    ProductName varchar(100) not null,
    Rate int not null
);

Your C# could then be:

public async Task<IEnumerable<Tuple<string, int>>> UpsertProductCostAsync()
{
    var products = new List<Product>
            {
                new Product {ProductID = 1, ProductName = "Tea", Rate = 10},
                new Product {ProductID = 2, ProductName = "Coffee", Rate = 20},
                new Product {ProductID = 3, ProductName = "Muffin", Rate = 30}
            };

    // one approach to transforming a collection to a datatable.
    // you could find / create an extension method to do this more succintly
    var productsDt = new DataTable();
    productsDt.Columns.Add("ProductID", typeof(int));
    productsDt.Columns.Add("ProductName", typeof(string));
    productsDt.Columns.Add("Rate", typeof(int));

    foreach (var product in products)
        productsDt.Rows.Add(product.ProductID, product.ProductName, product.Rate);

    var mergeStatement = @"
            
MERGE INTO [dbo].[Products] AS target 
USING (select ProductId, ProductName, Rate from @products) as source 
ON (target.ProductID = source.ProductID)
WHEN MATCHED THEN
UPDATE
SET target.ProductName = source.ProductName,
    target.Rate = source.Rate
WHEN NOT MATCHED THEN
INSERT ([ProductID],[ProductName], [Rate]) 
VALUES (source.ProductID, source.ProductName, source.Rate)
OUTPUT $action AS MergeAction;

";

    _con.Open();

    var actions = await _con.QueryAsync<string>(mergeStatement, 
        new { 
            products = productsDt.AsTableValuedParameter("TVP_Product") 
        }
    );

    var results = actions
        .GroupBy(x => x, (y, z) =>
            new Tuple<string, int>(y, z.Count()))
        .ToList();

    return results;
}