Inserting rows into a table with multiple fields in the primary key, the last of which should autoincrement

Question

I am storing price data events for financial instruments in a table. Since there can be more than one event for the same timestamp, the primary key to my table consists of the symbol, the timestamp, and an "order" field. When inserting a row, the order field should be zero if there are no other rows with the same timestamp and symbol. Otherwise it should be one more then the max order for the same timestamp and symbol.

An older version of the database uses a different schema. It has a unique Guid for each row in the table, and then has the symbol and timestamp. So it doesn't preserve the order among multiple ticks with the same timestamp.

I want to write a T-SQL script to copy the data from the old database to the new one. I would like to do something like this:

INSERT INTO NewTable (Symbol, Timestamp, Order, OtherFields)
SELECT OldTable.Symbol, OldTable.TimeStamp, <???>, OldTable.OtherFields
FROM OldTable

But I'm not sure how to express what I want for the Order field, or if it's even possible to do it this way.

What is the best way to perform this data conversion?

I want this to work on either SQL Server 2005 or 2008.

Answer 1

This looks like a job for... ROW_NUMBER!

INSERT INTO NewTable (Symbol, Timestamp, Order, OtherFields)
SELECT
    ot.Symbol, ot.TimeStamp,
    ROW_NUMBER() OVER
    (
        PARTITION BY ot.Symbol, ot.Timestamp
        ORDER BY ot.SomeOtherField
    ) - 1 AS Order,
    ot.OtherFields
FROM OldTable ot

The PARTITION BY means that row numbers are unique for each group of Symbol and Timestamp. The ORDER BY specifies in what order the sequence is generated.