title: Incrementing sequence number on SQL insert
body: <p>Is there a simple way to insert multiple rows and increment a particular field, using SQL Server 2005?</p>
  
  <p>Note, I am <strong>not</strong> looking for a solution involving an <code>identity</code> column - see the bottom of the question for an explanation</p>
  
  <p>(The following schema and data have been replicated <a href="http://sqlfiddle.com/#!3/0a5a4/3/0" rel="nofollow">here in this SQLFiddle</a>.)</p>
  
  <p>For instance, consider the following table and data...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And the following data to be inserted...</p>
  
  <pre><code>DECLARE @FKID INT
  SET @FKID = 1
  DECLARE @NEWDATA XML
  SET @NEWDATA = '&lt;data&gt;&lt;text&gt;three&lt;/text&gt;&lt;text&gt;four&lt;/text&gt;&lt;/data&gt;'
  </code></pre>
  
  <p><strong>Can the following be written in such as way that the <code>SEQUENCE</code> field comes out as <code>1,2,3,4</code> instead of the <code>1,2,3,3</code> that is currently does?</strong></p>
  
  <pre><code>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  &lt;-- Issue
  
  -- Required result...
  PKID  FKID  MYTEXT  SEQUENCE
  1     1     one     1
  2     1     two     2
  3     1     three   3
  4     1     four    4
  </code></pre>
  
  <hr>
  
  <p>Update:</p>
  
  <p>In response to the comment by @marc_s...</p>
  
  <blockquote>
    <p>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....)</p>
  </blockquote>
  
  <p>The table in question will hold multiple sets of <code>SEQUENCE</code> values, each "set" based on the <code>FKID</code> value... therefore the table could hold data along these lines...</p>
  
  <pre><code>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
  </code></pre>