How can we Re-Generated sequencial user id in autogenerated column in sql.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

How can we Re-Generated sequencial user id in autogenerated column in sql. I have a database in which Emp_id is autogenerated column. I have deleted so many data so that Emp_id comes in un-sequence order i.e.
12
14
21
22
23
56...
So now i want this Emp_id comes in a numbering sequence i.e.
1
2
3
4
5
6.....

Posted 17-Apr-12 19:58pm

Mas11

Add a Solution

Comments

Prasad_Kulkarni 18-Apr-12 1:59am

You have to truncate your table to re-generate series..

Mas11 18-Apr-12 2:03am

Can you provide me a code.. Because their is 700 record in my table & i dont want to delete that.. Can it solve by simple sql query.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad_Kulkarni · Answer 1 · 2012-04-17T20:13:00

Solution 1

Try this:
1. Add a column to your table and make its identity true as shown below:

ASM

alter table Example
add NewColumn int identity(1,1)

2. Then remove column emp_id from your table.
and
3. rename newcolumn with emp_id

Hope this works..

Posted 17-Apr-12 20:13pm

Prasad_Kulkarni

Comments

Mas11 18-Apr-12 2:22am

but their is 700 data already filled in my table.. I hope this will sequence only a single row.

Prasad_Kulkarni 18-Apr-12 2:39am

Yes it will..Have you tried this..