Recreate missing record

[AlastairP]

I have an autoinc table that has had a record deleted and packed thereafter

To recreate the record in the same place I am thinking of the following:


copy structure extended to a another file & and create a duplicate of the table and all the records for backup

remove autoinc
drop primary key

copy all records after the deleted entry to a temp table
delete records after the insertion point
insert the missing record
copy back the records after the newly created record

turn autoinc back on
add primary key

Is this going to work or will this corrupt that data?

 

[Olaf Doschke]

Why?

A table is a set of records, the order of the records should not matter at all, not a tiny tidbit.

Open the table in the table designer exclusive, change next value for the autoinc column and then add that record with that ID, then change next value again to the real next value and you'll have the same ID again. SET ORDER TO primarykey and the data is sorted that way, there is no need the data is stored in that order in the DBF file physically.

If you want to enforce any physical order you can set an order, then COPY TO and the new copy of the table will have the current index order as physical order. So that would be your way out, if you insist.

Bye, Olaf.

 

[Dan Freeman]

I agree with Olaf that you shouldn't do this. Don't even think about it. The physical order of the table should NEVER matter.

But if it does, rather than COPY TO (or even SORT TO with no index set), you can navigate to the record *after* the one that's missing and INSERT BLANK BEFORE. Then fill in the missing key. Just note that this will then perform a physical move of every record after the one you're inserting. The command requires exclusive use and will be slow as molasses on a large table.

It's better to use indexes. It's what they're for.

 

[AlastairP]

Hi Olaf, Dan

Thank you