在代码中会插入很多行,代码会给我这个错误
[23000][2601]无法在具有唯一索引"IX_Estimates_EstimateNumber"的对象"dbo.Estimates"中插入重复的键行。重复键值为(10005(
代码:
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;
之所以出现此问题,是因为select子句中的select MAX(Number)+1 FROM EstimateNumber
无法按预期工作,并且总是为每行返回相同的值。由于存在唯一索引,它将阻止数据插入。您可以执行select语句来验证这一点。
您可以使用ROW_NUMBER()
来修复此问题。
示例sql代码如下:
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;
在这里,我使用了一个局部变量来保存max(Number)
,并使用row_number
将其递增。还将裁切器从嵌套选择移动到加入