在SQL插入递增序列号(Incrementing sequence number on SQL in

是否有插入多行，并增加一个特定的领域，使用SQL Server 2005一个简单的方法？

请注意，我不是在寻找一个涉及解决identity列-看到问题的底部作出解释

（下面的架构和数据已经被复制在这里这个SQLFiddle 。）

例如，考虑下面的表和数据...

CREATE TABLE #TEMPTABLE (
   [PKID] INT IDENTITY, [FKID] INT, [MYTEXT] VARCHAR(10), [SEQUENCE] INT
)
INSERT INTO #TEMPTABLE ([FKID], [MYTEXT], [SEQUENCE]) VALUES (1, 'one', 1)
INSERT INTO #TEMPTABLE ([FKID], [MYTEXT], [SEQUENCE]) VALUES (1, 'two', 2)

-- Table data
PKID  FKID  MYTEXT  SEQUENCE
1     1     one     1
2     1     two     2

而要插入的以下数据...

DECLARE @FKID INT
SET @FKID = 1
DECLARE @NEWDATA XML
SET @NEWDATA = '<data><text>three</text><text>four</text></data>'

可以在以下在这样写成的方式，该SEQUENCE场出来为1,2,3,4 ，而不是1,2,3,3目前所呢？

INSERT INTO #TEMPTABLE ([FKID], [MYTEXT], [SEQUENCE])
SELECT @FKID, 
       X.value('.','VARCHAR(10)'),
       (SELECT ISNULL(MAX([SEQUENCE]),0)+1 FROM #TEMPTABLE WHERE [FKID]=@FKID)
FROM @NEWDATA.nodes('/data/text') AS X(X)

-- Actual result...
PKID  FKID  MYTEXT  SEQUENCE
1     1     one     1
2     1     two     2
3     1     three   3
4     1     four    3  <-- Issue

-- Required result...
PKID  FKID  MYTEXT  SEQUENCE
1     1     one     1
2     1     two     2
3     1     three   3
4     1     four    4

---

更新：

在回答@marc_s评论...

身份将是2005年最好的解决办法...最好的解决方案是迄今为止 - 为什么你明确地排除它，坚持滚动自己？ （有重复造成的所有风险等....）

有问题的表将持有多套SEQUENCE的值，各自基于“设置” FKID价值......因此该表可容纳沿着这些线路的数据...

PKID  FKID  MYTEXT  SEQUENCE
1     1     one     1
2     1     two     2
3     1     three   3
4     1     four    4
5     2     ett     1
6     2     tva     2
7     2     tre     3

Answer 1:

我不能在2005年进行测试，但你应该能够使用CTE蛮好数的东西;

DECLARE @FKID INT
SET @FKID = 1
DECLARE @NEWDATA XML
SET @NEWDATA = '<data><text>three</text><text>four</text><text>five</text></data>'

;WITH cte AS (SELECT @FKID FKID, X.value('.','VARCHAR(10)') a, 
                  ROW_NUMBER() OVER (ORDER BY X) r
             FROM @NEWDATA.nodes('/data/text') AS X(X))
INSERT INTO TEMPTABLE ([FKID], [MYTEXT], [SEQUENCE])
SELECT fkid, a,
  (SELECT ISNULL(MAX([SEQUENCE]),0)+r FROM TEMPTABLE WHERE [FKID]=cte.fkid)
FROM cte;

SELECT * FROM TEMPTABLE;

其给出结果：

1    1    one     1
2    1    two     2
3    1    three   3
4    1    four    4
5    1    five    5

---

UPDATE

如果查询将永远只插入一个FKID，下面的简化版本将工作，以及（您当前的查询进行必要的修改突出显示）：

INSERT INTO #TEMPTABLE ([FKID], [MYTEXT], [SEQUENCE])
SELECT @FKID, 
       X.value('.','VARCHAR(10)'),
       (SELECT ISNULL(MAX([SEQUENCE]),0)+1 FROM #TEMPTABLE WHERE [FKID]=@FKID)
        + ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM @NEWDATA.nodes('/data/text') AS X(X)

的目的(SELECT 1)在ROW_NUMBER的ORDER BY子句是避免指定任何特定的顺序。 它可以改变其他的东西（例如，以X.value('.','VARCHAR(10)'如果需要的话）。

Answer 2:

怎么样

    INSERT INTO #TEMPTABLE ([FKID], [MYTEXT], [SEQUENCE])
    SELECT @FKID, 
               X.value('.','VARCHAR(10)'),
              (SELECT ISNULL(Count(*),0)+1 FROM #TEMPTABLE)
    FROM @NEWDATA.nodes('/data/text') AS X(X)

Answer 3:

尝试这个：

with data as (
  select * from ( values
    (1,1,'one'),
    (2,1,'two'),
    (3,1,'three'),
    (5,1,'five'),
    (6,1,'six')
  ) data(PKID, FKID, MYTEXT)
) 
select lhs.*, sequence = count(*)
from data lhs
left join data rhs on rhs.FKID = lhs.FKID 
  and rhs.PKID <= lhs.PKID
group by 
  lhs.PKID,
  lhs.FKID,
  lhs.MYTEXT

这产生：

PKID        FKID        MYTEXT sequence
----------- ----------- ------ -----------
1           1           one    1
2           1           two    2
3           1           three  3
5           1           five   4
6           1           six    5

文章来源: Incrementing sequence number on SQL insert