copying comlete records to new table 1

[DarrenWard]

I want to copy a table structure then copy selected records from the original table.

I can copy the structure no problems and then replace the fields one by one, but can not see an easy way of copying the entire record.

The reason that I am doing this is because of a problem I am having with a report, the table I am reporting on has sales order info in it, if I want to print one sales order I do;

sele table
set filter to SalesOrderNumber = NumberRequired
do report
set filter

this works fine if the data is towards the end of the table, for some reason the old the sales order the longer it takes to print!!! so I am going to put the data in a temp cursor and print it from there.

Dazz

GuiltyMaggot - The best rock band in the world!

 

[agit72]

DarrenWard,
try using scatter and gather memvar
something like this:

sele SourceTable
go top
do while !eof()
scatter memvar
sele TargetTable
append blank
gather memvar
sele SourceTable
skip
enddo

Agit Permana (08561052915)
Mitra Solusi Pratama, PT

http://www.msp-works.com

 

[blmeanie]

Scatter and gather are great if you have the temp file set up already (which it sounds like you are doing that on the fly too)

Another way would be to use the copy to command with the conditional statement to copy certain records (if you can narrow it down to a conditional statement).

If you use a do while loop and have many many records that have a particular characteristic that is defining why you are copying to the temp file then you could accomplish it in one step with the copy to command.

 

[BryonB]

Another technique that works well for me, at least to copy/insert new records, is to use SQL SELECT or COPY TO ARRAY to copy the desired records into an array. You can then use SQL INSERT to insert the record(s) into the second table/cursor from the array. This works if the two tables have the same structure.


The code looks something like:

SELECT <Table1>
DIMENSION laRecords(1,FCOUNT())
COPY TO ARRAY laTest NEXT 1

- or -

SELECT * FROM <Table1> WHERE <Conditions> INTO ARRAY laRecords

* Then, to insert the record(s) into the second table/cursor:
INSERT INTO <Table2> FROM ARRAY laRecords

NOTE: COPY TO ARRAY doesn't automatically re-dimension the array, so if you want the array to be LOCAL, you'll need to dimension it ahead of time for it to work.

Whichever technique you use, you have to be careful that you don't get more records in the result than be stored in an array!

 

[BryonB]

Oops! I almost forgot! You can also use SQL SELECT to copy the records directly to the second table:

SELECT * FROM <Table1> WHERE <Conditions> INTO TABLE <Table2>

- or -

SELECT * FROM <Table1> WHERE <Conditions> INTO CURSOR <CursorName>

As of VFP 7.0 you can also include the READWRITE clause to make the resulting cursor updatable. If the second table is to be thrown away when you're finished with it, a CURSOR works really nice (no cleanup code needed)!

 

[DarrenWard]

Thank you for your help, but does anyone know why the problem was occuring in the first place !

Dazz

GuiltyMaggot - The best rock band in the world!

 

[Mike Gagnon]

Darrenward

this works fine if the data is towards the end of the table, for some reason the old the sales order the longer it takes to print!!! so I am going to put the data in a temp cursor and print it from there.

Thank you for your help, but does anyone know why the problem was occuring in the first place !

Yes, SET FILTER is well know to slow things down. And the bigger the table, the slower it will get. As suggested, SQL statemments will only &quot;bring down&quot; the records it needs, without having to &quot;carry&quot; the whole table in the process. If you can in any situation avoid using SET FILTER TO.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[DSummZZZ]

As Mike stated, SET FILTER gets slower, the larger the table. A query is a great solution, especially if you have an index tag associated with the query clause:

sele * from table where SalesOrderNumber = NumberRequired
report form ....

But you can also use this method, which will still be quite fast, if you have an index on the appropriate fields:

sele table
report form MyReport ;
for SalesOrderNumber = NumberRequired;
to print


Dave S.

 

[Steve Bowman]

I agree with above post using the &quot;FOR&quot; clause is always the better way to use any command that excepts it providing there is an index that matches the condition. I have found that even building a temporary index prior to running the &quot;FOR&quot; clause is more efficient then many other options. Cleaner code with less to clean up after. Creating SQL works well also however speed is controled by index as well and adds a few more lines of code not to mention changing workarea back after compleation and closing cursor... i.e

#1
report form test.frx for Myfield=MyVar to printer

#2
MyWorkarea=select(0)
select * from MyTable ;
    where MyField=MyVar into cursor MyCursor

if Used("MyCursor")
   select MyCursor
   report form test.frx to printer
   use in MyCursor
endif

select (MyWorkarea)

I always choose 1 line of code over 9


Steve Bowman
steve.bowman@ultraex.com