SQL Server INSERT Multiple Rows

Declarations:

readonly Promotions newPromoSummer = new Promotions()
{
    PromotionName = "2020 Summer Promotion",
    Discount = 0.25M,
    StartDate = new DateTime(2020, 06, 01),
    ExpiredDate = new DateTime(2020, 09, 01)
};

readonly Promotions newPromoFall = new Promotions()
{
    PromotionName = "2020 Fall Promotion",
    Discount = 0.10M,
    StartDate = new DateTime(2020, 10, 01),
    ExpiredDate = new DateTime(2020, 11, 01)
};

readonly Promotions newPromoWinter = new Promotions()
{
    PromotionName = "2020 Winter Promotion",
    Discount = 0.25M,
    StartDate = new DateTime(2020, 12, 01),
    ExpiredDate = new DateTime(2021, 01, 01)
};

2) Inserting multiple rows and returning the inserted id list example

var query = DbContext.Promotions.Query((Promotions promo) =>
{
    var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));
    INSERT().INTO(set);
    var r = OUTPUT(INSERTED<Promotions>());
    VALUES(set.RowFrom(newPromoSummer), set.RowFrom(newPromoFall), set.RowFrom(newPromoWinter));

    return r;
});

foreach (var promo in query)
    Console.WriteLine((promo.PromotionId, promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));

But what we really want is inserting a generic collection, so called Batch Insert:

public void T2_Batch(IEnumerable<Promotions> promos)
{
    var query = DbContext.Promotions.Query((Promotions promo) =>
    {
        var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));

        INSERT().INTO(set);
        var r = OUTPUT(INSERTED<Promotions>());
        VALUES(set.RowsFrom(promos));

        return r;
    });

    foreach (var promo in query)
        Console.WriteLine((promo.PromotionId, promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));
}

< BACK | HOME