SQL OUTPUT Clause to move Dynamics GP prices to history

SELECT *
FROM iv00108_history;

DECLARE @ROWCOUNTER AS INT;

SET @ROWCOUNTER = 903843;

WHILE @ROWCOUNTER != 0
BEGIN
DELETE TOP (1000)
IV00108
OUTPUT DELETED.[ITEMNMBR], DELETED.[CURNCYID], DELETED.[PRCLEVEL], DELETED.[UOFM], DELETED.[TOQTY], DELETED.[FROMQTY], DELETED.[UOMPRICE], DELETED.[QTYBSUOM], DELETED.[DEX_ROW_TS] INTO IV00108_History
WHERE PRCLEVEL IN ('LIST');
SET @ROWCOUNTER = @@ROWCOUNT;
END

--select * from iv00108_history
SELECT *
FROM iv00108_history;


Create a history table with the same schema as the live table (might want to remove the indexes to keep size down).

Use the above script, supplying the price list name to move the prices from the live IV00108 table to the new custom table IV00108_history.

This moves the records in batches of 1000 to keep the transaction locking under control in the database. If too much information is locked (selected) we start going from row locks to page locks into table locks. Performance goes at and users start to complain at that point. The price table has three million rows in it so this is a wise precaution.

Add comment

Loading