Post reply

Creating identity field (for sequential number) on SELECT

  • February 21, 2006 at 4:12 pm

    #170711

    Hi,

    Is there a way to automatically create an identity field on SELECT for sequential number?

    Rather than creating a temporary table with an identity field than insert the values from the SELECT query, then returning this temp table instead?

    Thanks in advance.

    Urbis, an urban transformation company

  • February 21, 2006 at 6:12 pm

    #622486

    You can, but only in conjunction with the SELECT INTO statement, e.g.

    SELECT IDENTITY(int, 1,1) AS ID_Num

    INTO NewTable

    FROM OldTable

    See BOL for more info.

  • February 21, 2006 at 6:21 pm

    #622487

    That means still creating a temp table and returning the temp table instead.

    If that's the case I'll have to do it that way then.

    Thanks.

    Urbis, an urban transformation company

  • February 22, 2006 at 7:02 am

    #622601

    If the table has a unique key then

    SELECT (SELECT COUNT(*) FROM

    b WHERE b. <= a.), a.

    FROM

    a

    but check the performance though

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply