How can I innsert a record in the middle of other records?

[fmasin]

Hi,

I have a table of records, and would like to insert other records in the desired positions within the table. What is the code for inserting a record in the middle of other records...e.g to insert a record after record No. 261...?

Please, somebody help...! Thanks and regards,

Francis (f_masinde@hi-lo.co.uk)

 

[skoko]

Im not sure if u can.

Why u need that?

 

[rgbean]

Francis,
While there is a way, it requires exclusive use of the table and it can't have any indexes - unlikely in a multi-user environment. Is there a reason why an appropriate index can't be used for this "ordering"?

If you can get exclusive use AND the table is 'small' you can use INSERT. Note all the recent VFP help files don't provide the following (from the FPW 2.6a Help):

"INSERT Command
Inserts a new record into the current table immediately after the current record and displays the new record for editing.

Syntax

INSERT
[BEFORE]
[BLANK]

Remarks

INSERT is included for backward compatibility. Use APPEND or INSERT SQL instead.
If you issue INSERT without the BEFORE or BLANK clauses, an editing window is displayed so you can enter data into the new record. The new record is placed immediately after the current record; if the table has an index tag or index, the new record is placed at the end of the table.
If CARRY is SET ON and BLANK isn't included, data in the previous record is automatically copied into the new record.

Note INSERT isn't recommended for use with large tables because an insertion near the front of the table forces rewriting of nearly every record. This can take a very long time. Use INSERTSQL instead.

Clauses

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

 

[fmasin]

Many thanks guys for your replies....the reason why I would like to insert records is because there are some records that are missing and I would like them to appear in a certain order.

I will try Rick's method and see whether it works...I will let you know if it works or not.

Thanks and regards,

Francis (f_masinde@hi-lo.co.uk)

 

[fmasin]

Rick

Insert/append before cannot work. I get the following error: 'INSERT cannot be issed when row or table buffering is enabled or when integrity constraints are in effect.'

Any other alternatives..? Thanks and regards,

Francis (f_masinde@hi-lo.co.uk)

 

[DEDMOD]

If you're just making repairs, why not shut off the buffering while doing it? Dave Dardinger

 

[rgbean]

Francis,
If this is just a "one-time" fix, you could temporarily create an index on a new field (e.g. MyOrder), then add the records you need - supplying the ncessary values to "sort" it the way you want. Now you have two options, use the SORT command, or set the ORDER to this field and copy the records to a new table - they should now be in the order you want. Do the nccessary deleting of the extra fields and index. Delete / Rename the "old" Table, rename the new table and all should be as you need it.

If this is something that needs to be done on a regular basis, I'd really consider just using an index, or a Local View ordered by your ordering field.

Rick

 

[Eguy]

INSERT is almost always a bad idea. This command is part of FoxPro's Xbase legacy and generally leads to bad code.

The fly in the ointment is that the new record is physically inserted immediately following the current record. This means that every record following that position has to be physically moved to make room. On a large table, INSERT is a spectacularly bad choice.

INSERT can't be used with most of the cool stuff in Visual FoxPro. You can't INSERT in a buffered table, or in one that has rules, triggers, primary keys or candidate keys.

The moral, though, is the situation has to be pretty unusual before it's worth using INSERT.

Ed
Please let me know if the sugestion(s) I provide are helpful to you.
Sometimes you're the windshield... Sometimes you're the bug.

 

[jonatan]

Maybe you can modify it with another temporary table.You can copy data from number what u want until eof.And then you insert your new data, then you copy your old data in temp table back to the origin table.Quite messy right ... But maybe can help.. see ya ..!