Incrementing sequence number on SQL insert

Question

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 = '<data><text>three</text><text>four</text></data>'

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:

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

Yes, @Pieter, I'm sure it would be easier - unfortunately I'm stuck with 2005, as that is our minimum supported DB and a lot of our clients are still using it — freefaller
– freefaller, Commented Mar 20, 2013 at 17:20
Because, @marc_s, the sequence is directly related to the foreign key column. I.e. FKID=1 can have multiple rows with SEQUENCE=1,2,3 and FKID=2 can also have SEQUENCE=1,2. Sorry if I didn't make that clear in the OP — freefaller
– freefaller, Commented Mar 20, 2013 at 17:29
any reason you can't use a variable to do it? Declare it, and increment to it from your select statement? Write the variable at the end. — RandomUs1r
– RandomUs1r, Commented Mar 20, 2013 at 17:33
Sorry @Random, I don't fully understand how you'd use a variable... please feel free to provide an answer with your solution — freefaller
– freefaller, Commented Mar 20, 2013 at 17:37

Andriy M · Accepted Answer · 2013-03-22 08:02:54Z

4

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;

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.

edited Mar 22, 2013 at 8:02

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Mar 20, 2013 at 18:05

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andriy M Over a year ago

+1, although a CTE is unnecessary. Just removing the +1 from the subquery and adding +ROW_NUMBER() ... after the subquery would be enough, i.e. the expression for the SEQUENCE column would look like this: (SELECT ISNULL(MAX([SEQUENCE]),0) FROM TEMPTABLE WHERE [FKID]=cte.fkid) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)).

freefaller Over a year ago

Thank you, Joachim, for your answer, you were definitely in the right direction. But @AndriyM, you have hit the nail on the head, and that is exactly what I needed (although the [FKID]=ctr.fkid should be [FKID]=@FKID). If you make that an answer, I will happily mark it.

Andriy M Over a year ago

@freefaller: I would have posted my answer if it was really different from this one, which works fine for me by the way. The only issue with this answer I can see is that, while testing, Joachim added one more item, five, to the @NEWDATA XML but forgot to reflect that in the posted output. I would also argue that in this particular solution, which uses a CTE, both [FKID]=cte.fkid and [FKID]=@FKID work perfectly well. In time, however, you might want to modify the CTE to generate rows for multiple FKIDs, and [FKID]=cte.fkid would work better.

freefaller Over a year ago

Yes, I understand what you're saying @AndriyM - but the query will only ever deal with a single FKID at one time, so simply having the ROW_NUMBER() OVER (ORDER BY (SELECT 1)) does exactly what I need, without the use of the CTE. If you're happy for Joachim to get the mark, then I'm fine with that as well. Thank you both for your help

Andriy M · Accepted Answer · 2013-03-21 06:09:22Z

1

How about

    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)

edited Mar 21, 2013 at 6:09

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Mar 20, 2013 at 17:15

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

1 Comment

freefaller Over a year ago

Thanks @Charles, but same result, the initial count is used on both insert rows... so you still get 3 appearing twice. Also, this wouldn't take into account the (unlikely, but still possible) situation where the SEQUENCE isn't contiguous from 1

Pieter Geerkens · Accepted Answer · 2013-03-20 17:30:58Z

0

Try this:

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

yielding this:

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

answered Mar 20, 2013 at 17:30

Pieter Geerkens

11.9k2 gold badges35 silver badges54 bronze badges

2 Comments

freefaller Over a year ago

Sorry, @Pieter, I'm having trouble seeing how I can integrate your answer with my insert statement and XML data

Pieter Geerkens Over a year ago

(1) The with block simply supplies some test data for verification. (2) The remaining select statement is intended to replace the select clause in your insert statement.

Collectives™ on Stack Overflow

Incrementing sequence number on SQL insert

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related