I've recently been tasked with writing a Sage import tool that imports Quantity Price Break Discounts.
The Sage 200 tables in question are:
I wont bore you with schema information as it's not relevant to my question, suffice to say - the primary key in all 3 tables is a BigInt without identity set (sigh), 1 StockItem can have many Discounts and 1 Discount can have many Qty Discount Breaks.
Now then, to create an import routine i first had to analyse what Sage 200 did on SQL if you created Discount and Breaks manually in sage (using SQL Profiler). As i say, Sage 200 does not make use of Identity columns, instead it uses a counter table.
Inserting a new row into StockItemDiscount did the following:
UPDATE [Counter] SET [NextValue] = [NextValue] + 10 WHERE [CounterID] = 1
It then selects the new ID:
SELECT NextValue FROM Counter WHERE CounterID = 1
It then inserts the new row using the new value it just selected from the counter:
INSERT INTO StockItemDiscount (StockItemDiscountID, /.../) VALUES (@NewID, /.../)
My question is this: Why on earth is Sage doing it this way? what could possibly be the reasoning behind it? (Specifically the +10 THEN reading the value)
All the tables share the same counter too, so 5 rows in 1 table would results in a gap in the id's of another table - i'm just really at a loss as to why they do it like this?
The reason i ask: After inserting a row into StockItemDiscount
i then need to delete any related rows in StockItemQtyDiscBreak
& insert replacements - however, using SQL profiler i cant see incrementing of the counter table unless i insert 5 or more discounts (the 6th causes it to hit the counter table again, it's almost as if the Sage UI is reserving those 10 ID's using them for a variety of inserts then reserving an additional 10 as it needs them - this just seems very very odd to me?
Theory #1:
They are inserting on N0, and reserving N1-N9 for future edits?
Theory #2:
They are using N0 for a parent record, and using N1-N9 as child record ID's? Although you said all 5 tables use the same counter and method, so this doesn't seem likely.
Are there any rows where N1-N9 is being used?
Theory #1a & #2a:
They intended to implement one of these, and changed their mind, or they previously implemented this, and went away from this model, but never cleaned up their code / methods.