Question:

Hello all,

This should be pretty simple, but I haven’t been able to find a good example. I have two identical tables and I want to copy all fields from selected records from one and insert them into the other. Rather than list every variable and field, is there a shortcut that will copy them over?

so…

SELECT tableA
SCAN FOR somedate = ld_date
INSERT INTO tableB (*) VALUES (*)
ENDSCAN

Thanks in advance!

Solution:

As long as all of the fields in both are the same in each table, you don’t need to list them all in a SQL INSERT at least as far as the destination fields are concerned.

INSERT INTO dbf_name [(FieldName1 [, FieldName2, ...])]
VALUES (eExpression1 [, eExpression2, ...])

You can see the dbf_name field list is in brackets which means that’s optional.  However, this requires you list them all from the source table, tableA, that can be a PITA.

The second way to do it, assuming they match, is to scatter the record’s fields from TableA into a memory variables and then use the following syntax to get those memory variables to insert into TableB:

INSERT INTO dbf_name FROM MEMVAR

And, best of all, if you had VFP9 (I know you don’t), you could use the newer INSERT INTO with a SQL SELECT built into the command line to get it done with less overhead:

INSERT INTO dbf_name [(FieldName1 [, FieldName2, ...])]
SELECT SELECTClauses [UNION UnionClause SELECT SELECTClauses ...]

INSERT – SQL Command

http://msdn.microsoft.com/library/en-us/dv_foxhelp9/html/f9d15b20-eb9d-4c37-8d4a-d9d02c01eb56.asp?frame=true