Auto generating the id?

Question

Auto generating the id?

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
April 19, 2010 at 4:56 am

#231864

Dear all,
i need to generate the locations id depends up on the state as well as
track number is auto incremented
Samples Data Example:
[STATE] , [LOCATION ID],[TRACK NO]
LOS ANGELS 10000011
LOS ANGELS 10000012
LOS ANGELS 10000013
LOS ANGELS 10000014
CALIFORNIA 10000021
CALIFORNIA 10000022
CALIFORNIA 10000023
CALIFORNIA 10000024
Here I need to generate the Location id and Track no
automatically based on State..

Viewing 9 posts - 1 through 8 (of 8 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Buddy, request unclear.. can u pls post some visual representation of your desired result based on the location and track number??

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 2

Yes, Table name is Location_master

The columns are like :

STATE , LOCATION_ID , TRACK NUMBER, ETC..

HERE THE DATA's WILL INSERT INTO LOCATION_MASTER

BUT WE NEED TO GENERATE THE location_id and Track number auto

group by State,

WITH BELOW FORMAT ...

LOS ANGELS 1000001 1

LOS ANGELS 1000001 2

LOS ANGELS 1000001 3

LOS ANGELS 1000001 4

CALIFORNIA 1000002 1

CALIFORNIA 1000002 2

CALIFORNIA 1000002 3

CALIFORNIA 1000002 4

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Without Table structure, or sample data we cant proceed further buddy :(.. and thats the reason tho u have 20 views, u have only 2 replies...

But for a starter, i will give u this..

SELECT

ROW_NUMBER() OVER(PARTITION BY [STATE] , [LOCATION ID] ORDER BY [STATE] , [LOCATION ID]) AS TRACK_NUMBER ,

[STATE] ,

[LOCATION ID]

FROM

LOCATION_LOCATOR

Tell us if this helped you!!

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 4

Thank you so much..

My problem is track number generations, now i got a solutions , thanks... Location id i generated by cursor...

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Had u posted the necessary DDL, sample data and desired output, people would have replied and fixed your problem just at the wink of your eyes..:-)

Anyways, happy to have helped you and glad your issue is resolved...

Cheers!!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

Location id i generated by cursor...

this should have been mentioned earlier.i was only working with "state" column

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Bhuvnesh (4/19/2010)
Location id i generated by cursor...
this should have been mentioned earlier.i was only working with "state" column

Thats the problem when the requirement is unclear with no DDL or sample data or desired output.. hmmm..

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Saravanan_tvr (4/19/2010)
Thank you so much..
My problem is track number generations, now i got a solutions , thanks... Location id i generated by cursor...

Heh... yep... a cursor is definitely the way to go here. That way, you'll have something to fix in the future. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)