Refresh primary key

Question

Refresh primary key

Khalidhussain

Right there with Babe

Points: 764
More actions
August 14, 2003 at 2:41 am

#84118

hi
Could someone help me in following:
I have a system data table where I add system data, e.g. Title table have Mr, Miss, Mrs, Dr etc. the table has column called ID and its set to primary key. if i want to add new data 'Ms' between e.g. 'Mr' and 'Miss' it wouldn't refresh ID column and give me:
1. Mr, 3. Ms, 2. Miss
Is there any way i can refresh the ID column so it will like:
1. Mr, 2. Ms, 3. Miss
Many thanks

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Khalidhussain,

quote:
hi
Could someone help me in following:
I have a system data table where I add system data, e.g. Title table have Mr, Miss, Mrs, Dr etc. the table has column called ID and its set to primary key. if i want to add new data 'Ms' between e.g. 'Mr' and 'Miss' it wouldn't refresh ID column and give me:
1. Mr, 3. Ms, 2. Miss
Is there any way i can refresh the ID column so it will like:
1. Mr, 2. Ms, 3. Miss

two methods I can think of for this:

1. Maybe you can add a second int field 'Sorting Order', place there in the values in which the data should be sorted. Might be useful ?!? with respect for future enhancements.

2. DROP PrimaryKey constraint (and maybe IDENTITY property off the field, e.g. make it a simple int, change the values the way you like, add back PrimaryKey again (and IDENTITY).

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Khalidhussain Right there with Babe Points: 764 More actions · Answer 2

Khalidhussain

Right there with Babe

Points: 764

August 14, 2003 at 3:10 am

#469883

thank you, it works.

rachelc SSC-Addicted Points: 447 More actions · Answer 3

What about moving the data to a temporary table, resorting it into the order you want it, truncating the Title table and then re-inserting the data?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi rachelc,

quote:
What about moving the data to a temporary table, resorting it into the order you want it, truncating the Title table and then re-inserting the data?

works also.

Another approach, same result.

More useful than mine when there are many records to be refreshed

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]