Get next 2 values for sequence in single row

Question

MSSQL 2014

I am performing a split on records in sql. The key of the table MyTable is generated by a sequence MySeq. Given a set of records, I want to generate 2 new keys that I can then do some work with then insert child rows.

MyTable
+----+
| Id |
+----+
|  1 |
+----+
|  2 |

Now to select my two new keys:

SELECT Id,
       NEXT VALUE FOR MySeq AS ChildId1,
       NEXT VALUE FOR MySeq AS ChildId2
  FROM MyTable

I want:

+----+----------+----------+
| Id | ChildId1 | ChildId2 |
+----+----------+----------+
|  1 |        3 |        4 |
+----+----------+----------+
|  2 |        5 |        6 |

I get:

+----+----------+----------+
| Id | ChildId1 | ChildId2 |
+----+----------+----------+
|  1 |        3 |        3 |
+----+----------+----------+
|  2 |        4 |        4 |

I think the reason for the single run of the sequence per row has something to do with the design of the feature. For example, it looks like you can order the sequence runs separately from the SELECT.

I have a work around that is fine enough (update the table var after initial INSERT), but before I left it that way, I thought I would see if there is a more natural way to get the result I am looking for.

Answer 1

What you can do in this situation is make the sequence to increment by two

CREATE SEQUENCE MySeq AS 
INT
START WITH 0 
INCREMENT BY 2; 

and then do a:

SELECT Id, 
  NEXT VALUE FOR MySeq AS ChildId1, 
  1 + NEXT VALUE FOR MySeq AS ChildId2 
FROM MyTable