SELECT .... INTO TABLE <existing table>

SELECT .... INTO TABLE <existing table>

• Bobo1234

  You can't. If you want to add to an existing set, and the previous cursor is R/W (use the READWRITE keyword in SQL statement), then simply APPEND FROM the new temporary.
  

• RajDas

  Hi Andy,

  In old versions, I'd claim and could demonstrate:

  If "nofilter" or other techniques are used to create a true cursor, no matter the size is (fits in memory or not), a file on disk is allocated (it might just be the file handle). To tell it in pseudocode:

  * Table test has only one record with a single field of type logical

  select * from test  into cursor myCursor nofilter && or other true cursor techniques

  lcFile = dbf()

  _cliptext = m.lcFile

  * If you check for that file's existence you can't find it (file(), adir(), ... says it doesn't exist, explorer also doesn't show such a file).

  * Another VFP session

  handle = fcreate( < paste > )

  fails with access denied until cursor is closed in first VFP. If any attempt to create an index, update that file the "visible" evidences of file's existence begins. Back in  VFP5 days the full demonstration code was published on universalthread and I severe it was the case.

  However recently I tried that again (actually in a VFP class to show how cursor temp files work to students) and I was embraced my claim was failing. This time really the file doesn't exist and you can create a file with the same name from another session! It looks like now VFP doesn't create any disk file nor allocate a handle for that filename unless memory is sufficient to hold it. I haven't spent much time to check details but as of today I can't say VFP creates a file for cursors in any case (but it does for its cdx,fpt if you happen to have/create them).

  Now in 2nd session you can create that temp file and write anything irrelevant in it. Still in first session you can promote the cursor to be readwrite (ie: using old indian trick, use again), close the original and modify new one. Temp filename is still the original one! And filetostr() to that file and you get whatever you've written to it lowlevel in other session. Here is some simple demo code:

  *session 1 - I mean independent VFP launches on same machine

  SELECT * from customer INTO CURSOR xx nofilter
  _cliptext = DBF()

  *session 2 - filename was pasted from 1st
  handle = FCREATE("C:\DOCUME~1\CETIN\LOCALS~1\TEMP\0000562X00IB.TMP")
  FWRITE(m.handle,"hello")
  FCLOSE(m.handle)
  FILETOSTR("C:\DOCUME~1\CETIN\LOCALS~1\TEMP\0000562X00IB.TMP")

  * session 1
  USE DBF('xx') IN 0 again ALIAS xx2
  USE IN 'xx' && now it's readwrite
  SELECT xx2
  BROWSE && you can modify
  lcOld = "C:\DOCUME~1\CETIN\LOCALS~1\TEMP\0000562X00IB.TMP" && pasted again here
  DBF() == "C:\DOCUME~1\CETIN\LOCALS~1\TEMP\0000562X00IB.TMP" && TRUE
  FileToStr(dbf()) && "Hello"

  This is of course, just an academic discussion, how VFP works behind the scenes. Now what I really care is, one way or the other, cursors are the ideal "temporary tables" for almost all situations. That's what any experienced VFP developer agrees upon. We just find it harder to explain the slight nuances to the newcomers:)

  PS: When someone use a readwrite cursor and discovers that alter table fails on 2nd attempt if there is a LFN in it, I feel bad to say it's a case to use a table instead or do it in initial select:) Since this is documented it's not a bug but I hope VFP team can do something about it.

  Update:

  For starters here is a guide to pros/cons of a cursor:

  Cons:

  -If you need to pass data between data sessions use tables or other means (like objects, arrays etc. A single array can hold 65000 records pre VFP9 and more in VFP9. In theory that means you can pass multiple up to 65000 record tables with a single object. It doesn't mean you should jump to doing it just for you can do it).

  -If data has a LFN in it and you need to alter table then use tables ( if no LFN initially you can do a single alter table, 2nd attempt fails - in case it's an SPT cursor alter table is not allowed at all).

  Pros:
  -A cursor has all the capabilities of a table except the 2 above

  -Cursors are always used exclusively. Means higher perfomance.

  -You do not need to find unique names for your "temporary storage". VFP does that for you. To you its name is the more meaningfull alias you gave initially (ie: select * from customer into cursor myCustomer - to you it's "myCustomer").

  -When you're done with a cursor and close it VFP does the housekeeping for you (deleting temporary file, deallocating handles etc).

  -You can index them. It's much better for your temporary index needs, if any, rather than doing that with external temporary index files (well debatable but cursors win in most cases especially if you think you generally work with a subset of large data).

  -Others that I'm sure I forgot to mention - like cursors might prove to be faster than arrays, but not always. At least if you're coming from another language and want to bind data to say a grid, you shouldn't immediately think it'd be faster if you could bind to an array instead:)

  
  

• Poma

  >> I may be wrong but I thought I have access to more data manipuation functions if I use a table as opposed to cursor.

  I think you are wrong <g> There is no practical difference between a table and a cursor in VFP, especially if you use the READWRITE clause in the SQL that creates the cursor - the resulting cursor is then fully read/write (just like a table) but it is automatically released when the VFP Session ends.

  
  
  

• NewName

  Hi Cetin

  >>If they ever created on disk If they ever created on disk

  I assume you are referring to those cases where VFP will offer a filtered view in memory rather than create a physical disk presence -but doesn't using the NOFILTER clause always force a physical file to be created

  I have always understood that to be the case and have certainly not seen it fail to do so (at least, if it did, I didn't notice).

  
  
  

• js06

  Andy,

  I'm sure you'd do your own checks on it. I don't want to be misunderstood, I mean physical filename is not reliable. Another session might create,write etc to that file (and if it does, then the session that created the cursor doesn't cleanup that file).

  
  

• AndersBank

  If they ever created on disk (with some old versions they're always created or at least file handle is allocated provided that they're true cursors)...

  Actually it's one of the reasons why you should prefer cursors over tables for "temporary". I asked for a reason and there are reasons sometimes (ie: cursors cannot pass datasession boundaries).

  
  

• NeW2VB

  Is there a reason you're using a table and not a cursor

  select field1, field2 from myTable into cursor customer readwrite

  would do what you want in most situations.

  
  

• Jpmon1

  Hi Cetin

  Interesting little demonstration and I agree with your conclusion that it does look very much like the first creeation of a cursor is limited to the VFP session. Actually that would seem reaonable, because unless that were the case, VFP couldn't clean up cursors on exit.

  In other words if a cursor that is created in one session were usable in another, then VFP could not clean them up and we would simply have more temporary tables to deal with. So in order to make a cursor 'visible' across VFP sessions the 'indian rope trick' is still the only way to do it.

  I am glad that don't have a demonstrable exception to the rule that NOFILTER always produces a local disk file as I rely on that a lot and I would hate to have to start adding code to test for a physical file every time I create a cursor!

  Thanks for the confirmation.

  
  
  

• DRoden

  CetinBasoz wrote:

  Is there a reason you're using a table and not a cursor select field1, field2 from myTable into cursor customer readwrite would do what you want in most situations.

  Thanks.

  I may be wrong but I thought I have access to more data manipuation functions if I use a table as opposed to cursor.

  
  

• lka

  Alex Feldstein wrote:

  You can't. If you want to add to an existing set, and the previous cursor is R/W (use the READWRITE keyword in SQL statement), then simply APPEND FROM the new tremporary.

  I have tried a 2nd "SELECT ... INTO" statement the previously populated G_TempDBF(), which works.
  However there is a messagebox, saying "G_TempDBF exist. Replace ". How can I get VFP to replace it without prompting

  
  

• rene schrieken

  >>for starters, CURSORS are stored as .TMP files in the temporary folder whereas temporary tables are stored as .dbf

  But a cursor IS a DBF - it just has a different extension, that's all. For that matter VCX, SCX, FRX, MNX, PJX are all dbfs too - the only thing is that the extension determines how their special handling by VFP. So the only difference between a cursor and table is the location and the handling when VFP exits. That behavior is defined by the TMP extension but there is no reason why you cannot treat a cursor just like a table - you can even copy a cursor to a table if you want to persist it.

  I think you may be reading way too much significance into the 'differences'. I cannot remember the last time I used a temporary table in an application - probably not since the days of FP 2.0 for DOS. Unless you need to persist the data between foxpro sessions, there really is no reason not to use a cursor.

  This is even more true in VFP 9.0 than ever before because the CAST() function allows you to add columns of any data type to cursors as part of the SELECT statement (you couldn't handle Integers or Memo fields this way prior to VFP 9.0 - though there were other ways to do it, CAST() makes it much simpler).

  
  
  

• Radith

  >Is there a reason you're using a table and not a cursor

  for starters, CURSORS are stored as .TMP files in the temporary folder whereas temporary tables are stored as .dbf

  
  

• Javahar

  Hi,
  I am not sure what are you trying to achieve. However, you can use ZAP to delete all reocrds, and then use APPEND FROM or INERT INTO ... SELECT ... FROM ... statement to replace records.
  
  Beside, you can try to SET SAFTY OFF to prevent VFP prompt ask for overwrite.
  
  Anyhow, I would think cursor should be the best fit for you. May be you can post any command you want to use which not applicable to cursor
  
  HTH
  
  

• TRID

  More Like what (there are differences but slight). If you don't have a specific reason to use a table then go with cursors. For "temporary" tables they have advantages over tables.
  
• SELECT .... INTO TABLE <existing table>