Re-inserting a deleted sequenced record

CLIFFORD ILKAY clifford_ilkay at
Thu Apr 2 12:49:56 EDT 2009

Hash: SHA1

Brian wrote:
> Hello Everyone,
> Does anyone know if it is possible to re-insert a sequenced record into a
> file where the record had been deleted so that the sequence number will
> remain the same?  Another words...if I deleted sequence #1, I can re-insert
> a backup of the record into sequence #1...or force it into sequence #1?
> I know there wasn't a way to do this in the past, thought there might be a
> way now.  
> I'm using studio with a native datafile.

Hi Brian,

Barring some "Mischa Magic", the best way of dealing with this is to
export and import in such a way as to preserve your record sequence numbers.

1. Find the maximum RSN for each of your files.

2. Export your data.

3. Create a new data file.

4. Add a NOT NULL character column in each table and call it "old_rsn".

5. Write a script to create as many rows as your max RSN for each of
your tables. In the old_rsn column, put something like "@###@" for the
value. If the max RSN in table1 was 12345, you should have 12,345 rows
of placeholder data in that table now.

6. Edit the exported data and add any rows that are missing, such as the
one you said had been deleted but you'd like to restore.

7. Import your data but be sure to edit, not insert, using the RSN as
the key and replace the old_rsn column with the old RSN. In other words,
if you have a match on the RSN between the old data and the new
placeholder data, that row will be overwritten by the matching row you
have in your old data and old_rsn will be overwritten by your old RSN.

8. Once you've imported all your data, delete all rows where old_rsn =
"@###@" in each table.

You've now recreated your database with the same RSNs and the same
"holes" in the database as what you had before. Except for any rows you
may have added, the new database and the old one should be identical now.
- --

Clifford Ilkay
1419-3266 Yonge St.
Toronto, ON
Canada  M4N 3P6

+1 416-410-3326
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla -


More information about the omnisdev-en mailing list