Site icon Recall Business

Increase number into insert from select in SQL

In the code there is a lot of rows will be inserted and the code gives me this error

[23000][2601] Cannot insert duplicate key row in object ‘dbo.Estimates’ with unique index ‘IX_Estimates_EstimateNumber’. The duplicate key value is (10005)

Code:

INSERT INTO dbo.Estimates (EstimateNumber, Date, Comments, CustomerId)
    SELECT
        (SELECT MAX(Number) + 1 FROM EstimateNumber),
        po.DateReceived,
        po.Notes,
        (SELECT Id FROM Customers WHERE Name = po.Customer)
    FROM
        staging.PricingTable po
    LEFT JOIN
        dbo.Estimates Es ON Es.Date = po.DateReceived
    WHERE 
        Es.Date IS NULL;
Exit mobile version