Probably easy: Help auto_incrementing ID by alpheb

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Probably easy: Help auto_incrementing ID by alpheb

Author

Topic

mnsilence
Starting Member

6 Posts

Posted - 2007-08-23 : 13:40:06

Hi... I'm trying to fix something...

So here's the problem:
I inserted a bunch of names into a table and had the id auto-increment. However, I didn't put them in alphabetically. Therefore, the id's don't correspond to the names.

Is there any way I could write a line that'd fix this... like... in pseudocode, something like: INSERT into ID auto_increment ORDER BY last_name

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-23 : 13:46:37

You'd have to truncate the table and reinsert your data.
INSERT INTO yourtable
SELECT <columns>
FROM yourothertable
ORDER BY last_name

Did the table already have data in it when you inserted more?

Jim

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-23 : 13:48:53

why do you care what the identity value is?

It's meaningless except for joins

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mnsilence
Starting Member

6 Posts

Posted - 2007-08-23 : 13:53:49

jimf: I'm not sure what you mean by the last line

X00: See... the code was written in Java and is ridiculously complicated to edit and recompile. (The original author used Java, ANT, and Eclipse to make it... so every time you change something, you have to recreate the WAR file and redeploy it.) So I'd prefer to just like... write over the existing ID column after sorting by last names

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-23 : 13:57:08

OK, you need to drop the identity, reinitialize, probably every hour, to make sure it's in synch, with the data

What's in synch though?

Can us more details

Or, shoot the developer?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-23 : 14:01:19

If the table already had data in it, say 100 records and last ID = 100,and you added 100 more records so last ID = 200. You have 1-200 with no gaps. If you delete the last 100 records and reinsert, your ID column will go from 1-100, and then from 201 - 300. Like Brett says it doesn't really matter, unless there's some manager type that insists the numbers stay consecutive and without gaps. Also, If that ID column is of IDENTITY in SQL Server, you can't overwrite it.

Jim

mnsilence
Starting Member

6 Posts

Posted - 2007-08-23 : 14:01:38

Alright... well, lemme see if I can clear things up a bit. I should mention this is my first time working with php so bear with me. :-)

ID......Name
1.......Addison
2.......Adjodah
4.......Afework
3.......Agard

See how ID is off? I put Agard in before Afework. Just changing the two wouldn't be a problem, however, this is about 570 names and locations that I'd rather not have to retype. (The numbering is off in some places too where I deleted names)

mnsilence
Starting Member

6 Posts

Posted - 2007-08-23 : 14:03:21

Hahah... yeah... the ID is directly referenced in the code, jimf. They're linked to filenames of jpg pictures.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-23 : 14:04:11

-- Make a copy of your data
SELECT *
INTO #t1
FROM yourtable

TRUNCATE TABLE yourtable

INSERT INTO yourtable
SELECT *
FROM #t1
ORDER BY last_Name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-23 : 14:08:56

mnsilence, are you using SQL Server or MySQL as the back-end database. I ask because most people don't use SQL Server when they are using php.

If MySQL, keep in mind that this is a SQL Server site, so our solutions might not work on MySQL. There is a MySQL forum over at dbforums.com that might be able to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mnsilence
Starting Member

6 Posts

Posted - 2007-08-23 : 14:12:14

No PHP here. I meant to write SQL. Whoops! jimf, you rock! :-D Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-23 : 14:15:15

quote:
Originally posted by mnsilence

No PHP here.

Then why did you say this:

quote:
Originally posted by mnsilence

I should mention this is my first time working with php so bear with me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mnsilence
Starting Member

6 Posts

Posted - 2007-08-23 : 14:16:23

right... just noticed that... TOTALLY meant to say SQL

Hehe, sorry. (I edited the last one four seconds after you submitted the last post)

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-23 : 14:24:07

Well then use Jim's answer, but make sure to reset the IDENTITY seed

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-23 : 14:29:04

Brett, TRUNCATE resets the seed for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-23 : 14:35:20

quote:
Originally posted by tkizer

Brett, TRUNCATE resets the seed for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I, I, I

, knew that...it was a good lunch afterall

Is it not Friday?

In any case, if you have any RI, TRUNCATE won't work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources