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;