Όταν μας βγάζει σφάλμα ότι δεν μπορεί να αποθηκευθεί μία εγγραφή, γιατί η τιμή υπάρχει είδη και αναφέρετε στο Unique Index του ReferenceNumber.

update-reference-number

-- Update sequence current value with max reference number
-- example is for HEITEMS sequence
DECLARE @refno int = (SELECT
    CAST(MAX(HEITEMS.herefnumber) AS int)
FROM HEITEMS)
DECLARE @inc int = (SELECT
    CAST(current_value AS int)
FROM sys.sequences
WHERE name LIKE 'HEITEMS%')

WHILE (@refno - @inc) > 0
BEGIN
    SET @inc = @inc + 1
    -- This select statements must have the complete sequence name.
    -- You can get the complete sequence name by sys.sequences system table.
    SELECT
        NEXT VALUE FOR HEITEMS_RNM
END

-- Check max Reference Number
SELECT
    CAST(MAX(HEITEMS.herefnumber) AS int)
FROM HEITEMS

-- Check current value on sequence
SELECT
    CAST(current_value AS int)
FROM sys.sequences
WHERE name LIKE 'HEITEMS%'

-- Get all sequences with their current value and complete name
SELECT
    name,
    current_value
FROM sys.sequences