How to insert a record between two records 1

[Guest_imported]

Dear all
if you have a table in VFP 6.0 and have 10 records.

If I want to insert a record between the records two and three what to do
-A.Vadivel

 

[Salmano]

Dear A.Vadivel,

As far as I know, it is not possible, both append and insert-sql, inserts the record PHISICALY at the end of the table; I sugest you use indexes; so you can LOGICALY "insert" your record where you want, as long as the index is made so.

Salmano.

 

[Salmano]

Dear A.Vadivel,

If you have only 10 records, you could try append a blank record; so you'll have 11 records; after that you move the content of the 10th record in the 11th record, the 9th to the 10th, and so on... the 3rd record in the 4th and you will have a blank record between the 2nd and the 3rd record; this will work, but if your number of records in the table will increase dramatically, this solution will be the slowest ever

Salmano.

 

[ChrisRChamberlain]

A.Vadivel

Locate where you want to insert a new record, then:-

INSERT BLANK BEFORE

will insert a new record HTH

Chris

 

[AChiado]

Hi Chris

I've tried INSERT BLANK BEFORE and it works fine

BUT

WHERE IS DOCUMENTED???? I CANT FIND IT Andrea C.P.
Italy

 

[ChrisRChamberlain]

AChiado

I don't believe it has been documented in nearly 10 years.

INSERT isn't recommended for use with large tables because an insertion near the front forces a rewriting of nearly every record in the table.

INSERT BEFORE

inserts a new record and opens the editing window.

INSERT-SQL is the suggested but inappropriate alternative, but in the situation described by A.Vadivel, there will be no problem with using INSERT.

Also with modern hardware, the performance hit is probably now quite acceptable for all but the mega sized tables. HTH

Chris

 

[rgbean]

A.Vadivel,
One last comment, in a multi-user environment, to use INSERT BEFORE, you'll either need to have the table opened EXCLUSIVE or at least have noone else have any locked records (and they won't be able to access the table while it's being updated).

Also, if you have an index on, it's going to put the record at the end anyway! From the FPW help file:
"BEFORE

If you issue INSERT BEFORE, an editing window is displayed so you can enter data into the new record. The new record is placed immediately before the current record; if the table has an index tag or index, the new record is placed at the end of the table.

BLANK

If you issue INSERT BLANK, an editing window isn't displayed and the new blank record is placed immediately after the current record. If the table has an index tag or index, the new blank record is placed at the end of the table."

Rick

 

[wgcs]

As one additional comment, Most tables (especially ones that will often have items added to them) should have an index on them. It's this index that sets the order the records are processed in, regardless of the physical order:

USE MyTable
INDEX ON Field1 ASCENDING TAG Field1
INDEX ON Field2 ASCENDING TAG Field2
SET ORDER TO Field1
* Now, the records are ordered by Field1
SET ORDER TO Field2
* etc.