CODEWITHSUNDEEP
sqlsql-servert-sqlselectinsert
Mohammad
Mohammad

Reputation: 5

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;

Upvotes: 0

Views: 290

Answers (1)

an33sh
an33sh

Reputation: 1124

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

Upvotes: 1

Related Questions