I have a given table
id,word
I want the id to be in sequence 1,2,3,4,5...
currently there are gaps 1,3,9,10....
there is no foriegnKey yet
and I canceled Identity increment.
what is the best way to do that?
thanks
drop .. recreate identity column ????
Not sure what you mean when you say you "cancelled identity increment". Did you recreate the table with no identity property?
If you are using identity column, the way to eliminate gaps would be what @harishgg1 suggested, namely, drop and recreate the column. While that would guarantee that there would be no gaps, the order may not be what you expect unless you take precautions. Search the web for how to assign identity values in your preferred order.
Also, starting in SQL 2012 (or thereabouts), it is not guaranteed that identity values will be sequential. That is to say, there can be gaps after you stop and start the server. See here . The workaround is to enable trace flag 272, but that is a server level setting, and you may or may not want to enable the flag.
Alternatively you can use a sequence object .
- Why do you want to do that?
- What is the reason there are gaps?
- What will you use to determine which row should be 1,2,3 etc?
is this a one time thing or do you want to keep this for historical purposes? Not sure how big the table is, but you could always use Row_Number, although it will be slow. something like this:
Create table #t
(id int,
word varchar(10))
insert into #t values
(1, 'test'),
(3, 'test5'),
(4, 'test6'),
(7, 'test9'),
(10, 'test111'),
(100, 'test222')
select Id, Word,
row_number() over (order by id) as rownum
from #tIt seems I wasn't clear enough, so here is the full story.
I store in the table words and their translations.
and there is also an ID which is a primary key and and it is also auto inserted (Identity Increment)
The ID is also the mp3 file name. (the files are stored in a folder)
I have already thousands of words in the table. (5906)
Because I deleted some records from the table there are gaps (eg. there are no records with id=5, or with id=6)
when I delete a record I delete his matching file too)
there is no problem so far.
I inserted an additional 1000 words to the table (i didn't have their matching sound files)
I deleted records... let's say about a 100 ( and again I have gaps)
so I have 900 words the first one's id is 5907, the last one is 6006
now I created their matching sound files (900)
their names are a sequence of numbers,
when I create the sequence I start from 5906.
If there where no gaps, everything is ok, and all id match the correct sound file (because I recorded them according a list I took from the table - order by id)
but because of the gaps i have a problem.
what I did, I removed the primary key and the identity increment from the id field (in my case it's posible this table is not connected to any other table)
I moved all the records after id=5906 to a new table (eWords3) and as an id i added the row_number
I added 5906 to each id
and inserted all those records to the original table (eWords2).
SELECT row_number() over(order by id) as id,word,wordHeb
into eWords3
FROM eWords2
where id>5906
order by id
delete FROM eWords2 where id>5906
update eWords3 set id=id+5906
insert into eWords2 select * from eWords3
it works but it's very complicated.
It's not working!
harishgg1, JamesK
your solution is not good for me as I want to apply it to part of the table (starting where id>5907)
yosiasz
I want to keep the original order (order by id) because according to this order I created the sound files
mike01
this query doesn't update the id column
and how I apply it to let's say start from id 7
thanks
imo I think this is more of a design issue than a SQL issue and is not sustainable. you probably should look for another column to try and associate with your files. Your design is tightly coupled with an ephemeral column identity column which you go right around and delete, impossible to maintain because tomorrow you will delete more rows and then it will be a nightmare to maintain this. you are using an identity column for something it was not intended to be used . PK should mainly be used to create relationships between tables.
1 Like
It's not going to happen again.
I insert new records (they are sequence)
I create their files (also a matching sequence)
and if after word i delete records there is no problem.
The problem happened because I deleted records and afterward I created the sequence of files.
i thought there is a simple solution somthing like
update eWords2 set id = (select max(id) as maxId from eWords2 where id>maxId )
but if there is not I will think it over.
hi elic
please dont mind my saying this
i am having a tough time understanding what your issue is ..
if i understand than I can think and come up with a solution
is it possible to do this by screenshots
giving simple examples
like this
drop create data ...
use tempdb
go
drop table A
go
create table A
(
id int identity(1,1),
name varchar(100)
)
go
insert into A select 'har'
insert into A select 'pra'
insert into A select 'rav'
insert into A select 'dadu'
go
select * from A
go
delete from A where id = 3
go
select * from A
go
here i deleted 3... i want to insert from 3
hi elic
i think this is what you are trying to say
you first created words like this
then
deleted word "please"
then created sequence
Here 3 shoud be for deleted word "please"
but 3 is now for word "fine nice"
am i correct in understanding ????
I created sequence, but not in the table. I have a folder with sound files, those files have names, and their name is a sequence 1.mp3, 2.mp3, 3.mp3 etc...
I must create them like that, because I split a long file to pieces and each file gets is name automatically.
now if I want to match the ids with the file names I have a problem.
the ids is not a sequence but the file name is a sequence.
ok elic
why not make the id's in sequence first then match with the filename
below is just an example of text files in folder with names .. just like your mp3 files
Usually that's what I do.
But this time I added records and, before I created the sound files I deleted some of them
Now when I create the sound files there won't be a match unless I close the gaps in the id's
hi
please take a look at this article
hope it helps
1 Like
I would recommend
- come up with a better image file naming convention instead of 1,2,4 etc ?
- wipe your database clean (in development) then use powershell to iterate through the folder and recreate the rows in the database.
If an ID field has meaning other than just an ID for a particular record then you are using ID's WRONG. An ID field should NEVER have meaning other than identifying a row of data. You should redesign so that the ID does not have meaning.