Incrementing / identity without identity insetr

  • hi

    if i want to do something like:

    select

    , *

    into

    myNewTable

    from

    myOtherTable --this is actually a linked server to an excel spreadsheet

    how would i do it? specifically the bit. I know sqlserver has newid() but that isn't a number or sequential.

    i can't create a table to insert into beforehand as i don't know how many columns i'll be getting back from myOtherTable (which is actually a linked server to an Excel spreadsheet over which i have no control). if you're wondering why i even need an incremented number column it's cos i wanna guarantee the order of my rows so they're the same order as in the spreadsheet that the linked server is pointing too. it;s a really messy spreadsheet and this is just the EXTRACT part of my ETL process.

  • You could either use row_number() or you can use identity in select into clauses. E.g.:

    select

    identity(int, 1,1) as id, *

    into

    myNewTable

    from

    myOtherTable

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • ho ho ho!! 🙂 excellent solution!

    brilliant! thanks so much andras!! much appreciated.

  • Above solution is great but how about this... I am in middle of project and get stuck on how to add incremental..

    Here is the detail.

    MyTable:

    Customer_Id, CustomerLineNo, CustomerName,

    12, null, ReliableAutomotive

    12, null, ReliableAutomotive

    12, null, ReliableAutomotive

    13, null, Wi-fi SupperStore

    13, null, Wi-fi SupperStore

    Above table has CustomerLineNo column where I would like to add incremental start from 1 by each Customer_ID, so, for Customer_ID =12, I should have below output and when new customer_Id comes it start with 1 again..

    Final Result should look like:

    Customer_Id, CustomerLineNo, CustomerName,

    12, 1, ReliableAutomotive

    12, 2, ReliableAutomotive

    12, 3, ReliableAutomotive

    13, 1, Wi-fi SupperStore

    13, 2, Wi-fi SupperStore

    How hard it is to code this type of query structure...?

    Any help,advice would be appriciated...

  • Check out ROW_NUMBER, which Andras also mentioned in his post.

    Here's a hint: ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Customer_ID)

    Edit: Typo.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Andras...this is great. I appriciated for point me in right direction. Just got learn something new...

    Thanks a Billion......:-P

Viewing 6 posts - 1 through 5 (of 5 total)

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