How to generate sequence number for a set of rows while Insert?

  Kiến thức lập trình

I have a SQL server Table. I want the records look like the example below.
I want to retrieve the TrainType values in the same order they were inserted.
I am thinking to attach a sequence number column to guarantee the order of retrieval to match the order of insert.

Alternatively, I would like to know as well, is there a better approach than attaching a sequence number ??

enter image description here

This is a sample insert.
This uses xml to populate Speed and TrainType columns.
I wish to add the Sequence column to this Insert statement and for which i am looking for the query that i will need to populate the Sequence number

INSERT INTO BSpeedRestriction
            (UID
            ,[Version]
            ,Speed
            ,TrainType,
            *[Sequence]* <<--
)
SELECT  @UID,
        @Version,
        a.c.value('Speed[1]','int') as 'Speed',
        a.c.value('TrainType[1]/@Numeric','int') as 'TrainType',
        ??? <<--
FROM    @BSpeedsXml.nodes('/BSpeed/Restriction') a(c)

  • sql

LEAVE A COMMENT