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.