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

Question

我想在插入多行时增加一个变量。

这是我最后一次尝试。

DECLARE @i int 
Set @i=0 
Insert into winners (ContestantID, DrawingID, SlotNumber) 
SELECT TOP (@Total) ID, @DrawingID, @i + 1 FROM Contestants 
where [email protected] 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()