I am running below query, sometimes these query skip few records to insert into newordersholdentry
and didn't get any error.
but if run same query again after finding it is missed some records for order, it will insert all.
Please let me know what could be the reason.
INSERT INTO newordersholdentry
(itemid,
lookupcode,
description,
gamacode,
ordered,
IsForceItem,
Scanned,
Location,
SortOrder
)
SELECT ID,
ItemLookupCode,
Description,
GamaCode,
SUM(Qty) AS Qty,
ForceItem,
0 AS Scanned,
SubDescription1,
Sortorder
FROM
(
SELECT Item.ID,
Item.ItemLookupCode,
Item.Description,
NewOrderItems.GamaCode,
NewOrderItems.Qty,
Item.SubDescription1,
Item.Binlocation,
ISNULL(
(
SELECT TOP (1) SortSno
FROM NewOrderPickPackSorting
WHERE(Bin = LEFT(Item.SubDescription1, 3))
), 99999) AS Expr1,
0 AS ForceItem,
p.sortorder
FROM NewOrderItems(NOLOCK)
INNER JOIN Item(NOLOCK) ON NewOrderItems.GamaCode = Item.SubDescription2
LEFT OUTER JOIN pickpath(NOLOCK) p ON concat(RTRIM(p.aisle), '-', p.section) = UPPER(LEFT(item.subdescription1, 6))
WHERE(NewOrderItems.Discontinue = 0)
AND (NewOrderItems.OrderID = 123456)
) AS t
Group by ID
, ItemLookupcode
, Description
, GAMACODE
, ForceItem
, Expr1
, BinLocation
, SubDescription1
, sortorder
question from:
https://stackoverflow.com/questions/65945321/insert-occasionally-missing-rows 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…