sql query manual increment

Question

sql query manual increment

ayi_102

Mr or Mrs. 500

Points: 563
More actions
February 4, 2009 at 4:49 pm

#200889

Hi,
i am doing my database project using SQL Server 2005
but i made a mistake since i dont use primary key
this is the problem
ID Name Address Phone
1 Andy 11 Main Road 123456
3 James 12 Queen Street 121421
4 John 9 Main Ave 523523
5 Andrew 44 Drive Ave 353178
6 Jill 12 Corner Street 245267
8 Bryn 10 Hill Road 421676
the reason i dont use PK because it is already to late
question ???
if i want the following ID increment + 1 when i insert new data
what sql query should i type?
ID Name Address Phone
9 aaa ssssss 11111111
10 bbb jjjjjjjjjj 33333333
11 rrrr ttttttt 33332122
and so on
thank you for the help
regards,
ayi

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

first it's not too late. It is never too late to fix an issue and make something better.

it is very easy to fix, you just have to break out of your mental box...you are afraid to make changes. Everyone fears change, but it's not an insurmountable problem.

you can even do it in the GUI, and let the system do all the heavy lifting....

In SQL Server Management Studio, drill down to the database and then your table, then right click on the table and choose "Modify", go down to Column Properties...find the section that says Indentity Specification, expan d the plus sign

make that first column have an "IS Identity" property to true.

that's it! half a dozen mouse clicks, and you'll fix it the recommended way.

behind the scenes, the system will make a new table, migrate the data, add any foreign keys and poof! you are all set.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

raj acharya SSCrazy Points: 2805 More actions · Answer 2

Hi,

I know why u feel fear do just one thing make one new table with same column and format of that table with identity(1,1)

now take all data from that table and insert that into newly crated table your problem solved.

you can set identity into current table aslo but if you have already enter id column into that then you should do as per i suggest .....

Raj Acharya

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

And one more thing if you are feared that something would go wrong when you change it is always a good practice to backup your data before any change, you can get back data whenever you want. Practice that.

But in your case it should not matter much as advised by Lowell

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 4

Its not too late..simple add one Identity column to have PK. this will make your life easier.

If you dont put PK now then its going to be too late in your project 😉

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Dugi SSCoach Points: 17998 More actions · Answer 5

ayi_102 (2/4/2009)
Hi,
i am doing my database project using SQL Server 2005
but i made a mistake since i dont use primary key
this is the problem
ID Name Address Phone
1 Andy 11 Main Road 123456
3 James 12 Queen Street 121421
4 John 9 Main Ave 523523
5 Andrew 44 Drive Ave 353178
6 Jill 12 Corner Street 245267
8 Bryn 10 Hill Road 421676
the reason i dont use PK because it is already to late
question ???
if i want the following ID increment + 1 when i insert new data
what sql query should i type?
ID Name Address Phone
9 aaa ssssss 11111111
10 bbb jjjjjjjjjj 33333333
11 rrrr ttttttt 33332122
and so on
thank you for the help
regards,
ayi

As I can see you have values that maybe they are related to other table according ID!

So you want to start with incremental values from the existing values!

You just go to the design table - go to the ID column and on the Column Properties set Identity Specification to Yes And Expand it and Set the Identity Increment 1 and Identity Seed 1!

Just save the table and that's it!

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]