SQL插入时递增序号 [英] Incrementing sequence number on SQL insert
问题描述
是否有使用SQL Server 2005插入多行并增加特定字段的简单方法?
Is there a simple way to insert multiple rows and increment a particular field, using SQL Server 2005?
请注意，我不是正在寻找涉及identity列的解决方案-请参阅问题底部的解释
Note, I am not looking for a solution involving an identity column - see the bottom of the question for an explanation
(以下模式和数据已复制在此SQLFiddle中的此处. )
(The following schema and data have been replicated here in this SQLFiddle.)
例如，考虑下表和数据...
For instance, consider the following table and data...
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
要插入以下数据...
And the following data to be inserted...
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?
Can the following be written in such as way that the SEQUENCE field comes out as 1,2,3,4 instead of the 1,2,3,3 that is currently does?
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
更新:
Update:
响应@marc_s的评论...
In response to the comment by @marc_s...
身份将是2005年最好的解决方案……迄今为止最好的解决方案-为什么您明确排除它并坚持自己滚动? (存在引起重复的所有风险，等等.)
Identity would be the best solution for 2005... best solution by far - why do you explicitly exclude it and insist on rolling your own? (with all the risks of causing duplicates and so on....)
有问题的表将保存多组SEQUENCE值，每个集"均基于FKID值...因此，表可以沿这些行保存数据...
The table in question will hold multiple sets of SEQUENCE values, each "set" based on the FKID value... therefore the table could hold data along these lines...
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
推荐答案
我无法在2005年进行测试，但是您应该能够使用CTE进行编号；
I can't test on 2005, but you should be able to use a CTE just fine to number things;
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
更新
如果查询仅插入一个FKID，则以下简化版本也可以使用(突出显示对当前查询的必要更改):
If the query will ever insert only one FKID, the following simplified version would work as well (the necessary changes to your current query are highlighted):
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)
ROW_NUMBER的ORDER BY子句中(SELECT 1)的目的是避免指定任何特定顺序.如有必要，可以将其更改为其他名称(例如X.value('.','VARCHAR(10)').
The purpose of (SELECT 1) in the ROW_NUMBER's ORDER BY clause is to avoid specifying any particular order. It can be changed to something else (e.g. to X.value('.','VARCHAR(10)'), if necessary.
这篇关于SQL插入时递增序号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
