| Author |
Topic |
|
jqtroy
Starting Member
5 Posts |
 Posted - 2006-02-23 : 01:32:13
|
| Hi to all,I'm a new member here and i would like to ask for some help regarding my problem. first i ahve an incremental primary key with format to something like this: 001-01-001, 001-01-002, 001-01-003, etc. My problem is that i want to insert (supply) the 'missing' or 'gaps' in my primary key field like for example: ..., 001-01-067, 001-01-068, 001-01-070. i want to insert the value 001-01-069 after the record 001-01-068. I have several gaps some ranging from several numbers like 005-04-007,005-04-020 which has a 13 records gap. Is there a way for stored procedure to solve this one?Thanks in advance. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-23 : 02:12:10
|
Welcome to SQLTeam jqtroy."My problem is that i want to insert (supply) the 'missing' or 'gaps' in my primary key field"I think you problem should be find the missing / gaps in your primary key"i want to insert the value 001-01-069 after the record 001-01-068"Records are not stored in a table in any specific order. You decide how the sequence of records during retrieving of your records by using the ORDER BYExample :select * from yourtable order by somecolumn Some Question1. Is your primary key value has fixed format ? like [numeric][numeric][numeric][dash][numeric][numeric][dash][numeric][numeric][numeric] ? 2. Is the number runs from 001-01-001 to 01-01-999 then follows by 01-02-001 ?3. Is the number always starts from xxx-xx-001You can get some hint on finding gaps in sequence numbers here. http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html----------------------------------'KH'It is inevitable |
 |
|
|
jqtroy
Starting Member
5 Posts |
Posted - 2006-02-23 : 02:35:16
|
| Hello khtan,thanks for your reply. regarding to your reply:*'I think your problem should be find the missing/gaps in your primary key - we can't find it or use ORDER BY since the missing/gaps is none existing. that is why i want to auto insert all gaps in the rows even though there are no records yet.*the Order By cannot be use yet since there are really gaps or records not yet supplied. what i want is to already include the rows( records) not yet filled up so that we can determine (count) the rows still available to filled up. the table has 20 fields.answer:1. the table comes from a dbase or clipper format which i imported in sql server. I still don't know yet how to format a fields (esp. primary key, auto_num)2. yes and what i suppose want to happen but since the table of all records is not yet complete, we already want to include the primary key even if the other fields are all blank or null, meaning the primary key should be already there 3. yes the number always start with xxx-xx-001. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-23 : 03:01:39
|
| why can't you use an ORDER BY? I'm not understanding this question very well. I'll ignore that bit for now..Do the values in that column have any meaning and/or are they used as foreign key in any child tables? If not, just smack those values and update with new ones. -ec |
|
|
|
jqtroy
Starting Member
5 Posts |
Posted - 2006-02-23 : 19:27:17
|
| yes i can use order by but, it only arrange the order or increment of the records and did not solve my problem on how to insert data in all the rows. actually my records are in order by already and you can see that they are in order but there are gaps (sorry for using the word missing previously). for example row number 001-01-006 is followed by 001-01-008 and my problem is how to insert/add record number 001-01-007 (rest of the fields are blank yet). some even have gaps ranging from 1 to several hundreds and i want to fill it up automatically (example: 009-10-010 followed by 009-10-100 and i want to fill in between the records 009-10-011 to 009-10-099). |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-23 : 22:37:37
|
here is a simple script to find the missing no. You will need to expand it to your requirementcreate table #table( pk varchar(10))insert into #tableselect '001-01-001' union allselect '001-01-002' union allselect '001-01-005' union allselect '001-01-006' union allselect '001-01-010'select pk, convert(int, right(pk, 3))from #tableselect '001-01-' + right('000' + convert(varchar(3), NUMBER), 3) as missing_pkfrom dbo.F_TABLE_NUMBER_RANGE(1, 999) left join ( select pk, right(pk, 3) as pk_int from #table where left(pk, 6) = '001-01' ) as a on NUMBER = a.pk_intWHERE pk_int is nullThe Number Table Function, F_TABLE_NUMBER_RANGE can be obtain from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685----------------------------------'KH'It is inevitable |
|
|
|
jqtroy
Starting Member
5 Posts |
Posted - 2006-02-24 : 02:48:02
|
| thanks KH i'll try your script and hope it will work out. |
|
|
|
sal
Starting Member
6 Posts |
Posted - 2006-02-24 : 13:30:31
|
| Um, why would a primary key have gaps? You shouldn't be able to add a record without one, or I'm misunderstanding the question. I'm new to SQL Server, but in Access, anyway, that's how it works. |
|
|
|
jqtroy
Starting Member
5 Posts |
Posted - 2006-02-28 : 02:54:53
|
| originally i imported the database from access. it is not yet the primary key then and due to some operation of delete during the course of my work, i need to delete some records/rows as it becomes irrelevant or obsolete. so the database will really have gaps in the end. now that it is in sql server format i am required to fill up the gaps again to reuse the fields being deleted before in access format. never mind the other fields to be blank as long as i can insert the multiple rows with the field required having format as above. the format did not change only that i need to put it back (add/insert). |
|
|
|
|
Insert Records Into Gaps In Primary Key