Inserting data into one SYS(2015) file, need order and records that meet a certain criteria 1

[Steve-vfp9user]

Hello

I am trying to add records from several tables to a SYS(2015) file that meet a certain criteria:

* Using mmastlink=525 as an example

USE CAACTIVITY SHARED
COPY STRUCTURE TO tempfile+'.dbf'

SELECT * FROM CAACTIVITY WHERE MASTLINK=mmastlink ;
  ORDER BY ACTDATE INTO TABLE tempfile+'.dbf'

This works as expected

If I issue the below from a different table it
removes the above records but adds the ones below:

SELECT * FROM CAEXPREP WHERE MASTLINK=mmastlink ;
  ORDER BY ACTDATE INTO TABLE tempfile+'.dbf'

I have tried the first line and then replaced the second line with the below
which works but then the dates are not in the order they should be CAEXPREP records:

APPEND FROM CAEXPREP FOR MASTLINK=mmastlink

My question is, how do I insert records matching the relevant criteria (MASTLINK=mmastlink)
and show the records in their respective order (these are UK date format) as below:

From CAACTIVITY

20/06/2023
21/07/2023
01/08/2023
etc....

Then:

From CAEXPREP

16/06/2023
21/06/2023
04/08/2023
etc....

The help file shows a command called INSERT but it doesn't like (ALL)

INSERT INTO tempfile+'.dbf' (ALL) ;
  SELECT * FROM CAEXPREP ;
  WHERE MASTLINK=mmmastlink

Any guidance would be appreciated.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Mike Lewis]

Steve,

The main thing to understand here is that [tt]SELECT ... INTO TABLE ....[/tt] creates a new DBF every time, even if there is a DBF with the same name already in existence. That explains your comment that "it removes the above records but adds the ones". The [tt]SELCT[/tt] will always overwrite the existing data.

For the same reason, your [tt]COPY STRUCTURE[/tt] is redundant. The [tt]SELECT ... INTO[/tt] implitly creates the required structure.

To answer your main question ....

The order in which you insert the records is pretty irrelevant. What you need to be concerned with is the order in which you want to retrieve the records (perhaps to display them in a form, or include them in a report). The easiest way to achieve that is to create an index on the final table. You do that with a simple [tt]INDEX ON[/tt] command, indexing on the relevant date fields.

Also, if the date field is an actual date data type (rather than a character field that happens to contain dates), then the date format (British or whatever) is irrelevant. Once you have created the index, the records will appear in the desired order.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

One other point ...

Given that your target table is named tempfile.dbf, I assume that the table is a temporary one. If so, it is usually better to use [tt]SELECT ... INTO CURSOR[/tt] that then[tt] SELECT ... INTO TABLE[/tt]. This has a couple of advantages:

- You don't need to worry about SYS(2015). The cursor name will be unique within your application, even if tables with the same name already exist.

- You don't have to delete the table when you have finished with it. It will be automatically removed when the program closes.

In all other respects, the code will be the same.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Mike

Thank you for your prompt posts.

I take on board your suggestions.

Much appreciated.




Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

I'd take it from a totally different untechnical point of view:

It really looks like a break from programming (for a few hours, perhaps minutes) would help you.

1. You talk of SYS(2015) in the thread title but don't show usage of SYS(2015) at all.
2.

* Using mmastlink=525 as an example

Well, you don't use 525 as an example. All your code uses the much more general [tt]MASTLINK=mmastlink[/tt] which will filter depending on whatever value mmastlink has. And that's fine, no problem.

But what I see from that is that you're not concentrated, at the moment.

It pays a lot to take[tt][/tt] breaks from working, even if you only code as a hobby.

I spare to add to the technical issues, Mike Lewis has covered that very well, already.



Chriss