sql查询相同数据 序号递增,SQL插入时递增序号

Is there a simple way to insert multiple rows and increment a particular field, using SQL Server 2005?

Note, I am not looking for a solution involving an identity column - see the bottom of the question for an explanation

(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 = 'threefour'

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

-- Required result...

PKID FKID MYTEXT SEQUENCE

1 1 one 1

2 1 two 2

3 1 three 3

4 1 four 4

Update:

In response to the comment by @marc_s...

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....)

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

解决方案

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 = 'threefourfive'

;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;

which gives the result:

1 1 one 1

2 1 two 2

3 1 three 3

4 1 four 4

5 1 five 5

UPDATE

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)

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.