| Author |
Topic |
|
leone
Starting Member
6 Posts |
 Posted - 2010-05-29 : 13:55:51
|
| Hey I need help organizing the id in the database. Right now when I look at the database, the id number for the rows are like 10 , 15, 23,40..... how can I reright them so 10 becomes 1 15 becomes 2.... I also have few other table in that database that uses info from that table so it would also need to change the id in that table also, to match the same id in the other table.so like id 15 in table one becomes 1 and the id 15 in table 2 becomes 1. What kind of sql script would i need to do this?thxs |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
leone
Starting Member
6 Posts |
Posted - 2010-05-29 : 17:56:58
|
| well cause getting weird errors like some data not saving and i am guessing it has to do with this. Before it was fine, but when i combined my database into one all this started to happen and now i have id that are like 10000000 with huge gaps |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
leone
Starting Member
6 Posts |
Posted - 2010-05-30 : 13:14:22
|
| o ok so anyway to fix this? |
|
|
|
Sachin.Nand
2937 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-30 : 13:48:45
|
| Does anything Reference/JOIN to those IDs? Because if you renumber them you will have to also renumber all the linked records in other tables.If you have merged several tables perhaps the "Next Value" for the IDENTITY is less than the maximum value?If so you can reset it to be the next higher number:check the current "next value"DBCC CHECKIDENT ('dbo.MyTable')get the current highest ID value:SELECT MAX(MyID) FROM dbo.MyTableif necessary reseed the IDENTITY to the number you want to resume from:DBCC CHECKIDENT ('dbo.MyTable', RESEED, 9999999) |
|
|
|
leone
Starting Member
6 Posts |
Posted - 2010-05-30 : 16:03:05
|
| thxs for the link going to check it out and yes their are other table in reference with that one table going to try the reset. I tried before resetting the auto increment back to one but still goes from 100000. But thxs for the tip going to check it out |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
How can I organize the id in the database