插入多行存储过程时增加变量

Question

我试图在插入多行的同时增加一个变量。

这是我最后一次尝试。

DECLARE @i  int
Set @i=0
Insert into winners (ContestantID, DrawingID, SlotNumber) 
SELECT TOP (@Total) ID, @DrawingID, @i + 1  FROM Contestants 
where Drawingid=@DrawingID ORDER BY NEWID()

Answer 1

使用ROW_NUMBER函数：

INSERT INTO winners (ContestantID
    , DrawingID
    , SlotNumber) 
SELECT TOP (@Total) ID
    , @DrawingID
    , ROW_NUMBER() OVER (ORDER BY ID)  
FROM Contestants 
WHERE Drawingid = @DrawingID
ORDER BY NEWID()