Increase number into insert from select in SQL-CodePudding

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;

CodePudding user response：

The issue is happening because select MAX(Number) 1 FROM EstimateNumber in the select clause will not work as you expect and always return the same value for each row. Since there is an unique index, it will block the data insert. You can execute the select statement to verify this.

You can use a ROW_NUMBER() to fix this.

A sample sql code is follows :

declare @maxval integer ;
select @maxval =  max(Number) from EstimateNumber ;

insert into dbo.Estimates ( EstimateNumber, Date,Comments, CustomerId )
select  @maxval   ROW_NUMBER() OVER (ORDER BY c.Id), po.DateReceived, po.Notes, c.Id
from staging.PricingTable po
join Customers c on c.Name = po.Customer
left join dbo.Estimates Es on Es.Date = po.DateReceived
where Es.Date is null;

here I have used a local variable to hold max(Number) and incremenetd it using row_number. also moved the Cutomers from nested-select to a join

CodePudding user response：

You can make the column "EstimateNumber" auto increment like this

ALTER TABLE dbo.EstimatesMODIFY COLUMN EstimateNumber INT auto_increment