Incrementing / identity without identity insetr

Question

Post reply

Incrementing / identity without identity insetr

sho-467341

Ten Centuries

Points: 1062
More actions
December 14, 2007 at 4:53 am

#201732

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.

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

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

sho-467341 Ten Centuries Points: 1062 More actions · Answer 2

ho ho ho!! 🙂 excellent solution!

brilliant! thanks so much andras!! much appreciated.

keyun SSCrazy Points: 2397 More actions · Answer 3

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

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...

Garadin One Orange Chip Points: 29613 More actions · Answer 4

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]

keyun SSCrazy Points: 2397 More actions · Answer 5

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

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