sql-server-2008 - SQL : Auto Increment value during insert

Question

我有一个现有的表，出于某种原因，设计者决定通过将最后使用的值存储在单独的表中来手动控制主键值(目前无法选择将表更改为使用 Identity)。

我现在需要对该表进行批量更新，如下所示:

DECLARE @NeedFieldID int
SET @NeedFieldID = 62034

    INSERT INTO T_L_NeedField (NeedID, NeedFieldID, FieldName, Sequence, DisplayAs, FieldPrompt, DataType, ValidOptions, IsRequiredForSale)
    (
        SELECT 
            DISTINCT n.NeedID, 
                @NeedFieldID + 1,           
            'DetailedOutcome',
            999,
            'Detailed Outcome',
            'Select appropriate reason for a No Sale outcome',
            'T',
            'Pricing, Appointment Date / Time Not Available, Will Call Back, Declined', 
            0
        FROM  T_L_Need n
            INNER JOIN T_L_NeedField nf
                ON n.NeedID = nf.NeedID
        WHERE (n.Need LIKE 'Schedule%' AND n.Disabled = 0)
    )

显然“@NeedFieldID + 1”不起作用(只是用它来显示我想做的事情)。当 SQL 插入每个不同 NeedId 的值时，如何递增 @NeedFieldID？我使用的是 SQL Server 2008。

Answer 1

你想要row_number() :

DECLARE @NeedFieldID int
SET @NeedFieldID = 62034

    INSERT INTO T_L_NeedField (NeedID, NeedFieldID, FieldName, Sequence, DisplayAs, FieldPrompt, DataType, ValidOptions, IsRequiredForSale)
    (
        SELECT 
            DISTINCT n.NeedID, 
                @NeedFieldID + row_number() over (order by n.NeedID),           
            'DetailedOutcome',
            999,
            'Detailed Outcome',
            'Select appropriate reason for a No Sale outcome',
            'T',
            'Pricing, Appointment Date / Time Not Available, Will Call Back, Declined', 
            0
        FROM  T_L_Need n
            INNER JOIN T_L_NeedField nf
                ON n.NeedID = nf.NeedID
        WHERE (n.Need LIKE 'Schedule%' AND n.Disabled = 0)
    )

但是，最好的选择是使 NeedFieldID 成为标识列，然后让 SQL Server 为您完成工作。