Inserting records in bulk, but check total quantity of all products first

Question

Post reply

Inserting records in bulk, but check total quantity of all products first

MountainEve

SSC Enthusiast

Points: 114

September 5, 2021 at 2:30 pm

#3927013

Lets say I am creating orders and each order can have same product.

I am inserting orders in bulk, but I need to check if total of all products for orders is not more than available quantity in stock.

In my trigger:

    DECLARE @Qty Int
    DECLARE @ProdID Int
    DECLARE @Quantity Int
    SELECT @Qty = Qty, @Prod=ProductID from inserted
    SELECT @Quantity = Quantity
        from existingproducts WITH (NOLOCK)
        where ID = @ProdID
    if @Qty>@Quantity
    begin
        RAISERROR ('not on stock
                16, -- Severity.
                1, -- State.
                @Qty,
                @Quantity
                );
        ROLLBACK TRANSACTION
    end

This would work fine with one record, but how do I do with bulk isnert?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 2

What you want to do is check all records from inserted. Note, this means that the entire commit size from bulk copy gets accepted or rejected. You can modify your query like this:

    If exists (SELECT ID
        from existingproducts e
         inner join inserted i
         where e.ID = i.id
         and i.Qty > e.Qty
    begin
        RAISERROR ('Some items are not on stock
                16, -- Severity.
                1, -- State.
                );
        ROLLBACK TRANSACTION
    end

If you want to return some info, what you could do is create your variables and get the TOP 1 from the query and store the ID and Qty in the variables and return that.

I guess you could use STUFF and concatenate all the values that have a larger quantity and return one long string, but that might cause other issues. I might just return 1 and get someone to then check the data before insert.

My suggestion would really be to bulk load into a staging table, join with the existing table and flag all records that are broken and report that. You could then update/insert all other records into the main table.

MountainEve SSC Enthusiast Points: 114 More actions · Answer 3

Alright, but how would SQL know that? - or my question may have not been clear enough.

Lets say I am importing 3 orders, one has Product A - 3 qty, another Product A - 4 qty and third Product A has - 2 qty?

orderlines is a table that I am inserting in, product lines have qty field for how many ordered

3+4+2 = 9, but on stock lets say I have 5 only, so I need to refuse then whole import in trigger

there is also general products table which has this qty of 5

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

may I ask why the stock level is not checked prior to committing/accepting the order ?

Maybe if you could explain in more detail how your order/stock system works, then it may help us all.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MountainEve SSC Enthusiast Points: 114 More actions · Answer 5

It is simple like that and a tiny app. We import some orders which may contain different quantities of same product. We receive orders (or better to say a set of collected inquiries) in bulk from different areas and check if we could provide. If we can, it imports all, if not cancels. How would we check prior to committing?

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 6

If you are only inserting one order at a time, batchsize=1, then either of these work if you are updating counts in inventory against sales.

If you are hoping you can bulk add multiple records and they will be checked, then you can't. what you need to do here is:

Sum all data to be inserted by product and compare with the amount in inventory.
For those products that haven't exceeded the inventory, now insert those records.

There is no good way to do this directly, and trying is going to get you into trouble. Bulk insert into a staging table, then do comparisons and decide what to move over.

ScottPletcher SSC Guru Points: 101108 More actions · Answer 7

Looks like you're trying to do something like below. Note that if multiple, different ProductIds are INSERTed at the same time, if ANY of them fail, ALL will be rolled back by the code below. If you need to allow multiple ProductIds in the same INSERT and cancel some and not cancel others, then you would have to use an INSTEAD OF trigger rather than an AFTER trigger.

Also, note that you must not use "WITH (NOLOCK)" in this case. I'm much more tolerant of it overall than are many other folks, but it would give you bad results in this case:

DON'T USE (NOLOCK) IN THIS CODE!

SELECT TOP (1) @ProdID = i.ProductID, @Qty = i.Qty, @Quantity = ep.Quantity
FROM (
    SELECT ProductID, SUM(Qty) AS Qty
    FROM inserted
    GROUP BY ProductID
) AS i
INNER JOIN dbo.existingproducts ep ON ep.ID = i.ProductID AND i.Qty > ep.Quantity

IF @ProdID IS NOT NULL
BEGIN
    RAISERROR('Product %i not on stock, order qty of %i but stock qty only %i', 16, 1, @ProdID, @Qty, @Quantity)
    ROLLBACK TRANSACTION
END /*IF*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".