how to change the series of id after insert data in sql server

Question

0.00/5 (No votes)

See more:

i have a table with some data, in this table id in not auto increment. Data is:
Id Name
1 abc
2 def
3 ghi

i want change the series form 1,2,3... to 101,102,103... through query
i want change my table like:
Id Name
101 abc
102 def
103 ghi

Help me to solve that problem
Thanx with Regards
Ankush Sharma

Posted 18-Jul-13 18:58pm

AnkushSharma100

Add a Solution

Comments

_Asif_ 19-Jul-13 1:05am

Your Id column is primary key?

AnkushSharma100 19-Jul-13 1:06am

yes, id is a primary key...

_Asif_ 19-Jul-13 1:18am

How many rows in the table? All Id in the table is increment by 1?

AnkushSharma100 19-Jul-13 1:22am

Table has more then thousands record...i wanna change all id with increment by 1 and started from 101

ArunRajendra 19-Jul-13 1:08am

Do you have references to other tables?

AnkushSharma100 19-Jul-13 1:09am

no sir..

walterhevedeich 19-Jul-13 1:11am

You can actually add to the Id column as long as it is numeric. Something like this
SELECT (Id + 100) AS 'Id' FROM Table

AnkushSharma100 19-Jul-13 1:15am

this query only show the data...but not able make changes in the table..

3 solutions

Solution 1

UPDATE table
SET id = 100 + id
FROM table

Posted 18-Jul-13 19:19pm

berrymaria

Solution 3

declare @temp int
set @temp=0
while(@temp<3)
begin
update tbl set ID=@temp+100+ID where ID=1+@temp
set @temp=@temp+1
end

Posted 18-Jul-13 19:43pm

AnkushSharma100

Comments

AnkushSharma100 19-Jul-13 1:48am

Solution of Zafar Safi is very nice..Thanx mr. Zafar Safi

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)

Zafar A khan · Accepted Answer · 2013-07-18T19:29:00

Solution 2

try this this will turn your series to the desired

SQL

update subject set subjectid=subjectid+100 
where subjectid=subjectid

Posted 18-Jul-13 19:29pm

Zafar A khan

Comments

AnkushSharma100 19-Jul-13 1:44am

Thanx sir..

Zafar A khan 19-Jul-13 2:59am

glade to help you