The INSERT-SQL command does not support using the SELECT-SQL statement in Visual FoxPro 7.0 and earlier versions (259274)

The information in this article applies to:

Microsoft Visual FoxPro for Macintosh 3.0b
Microsoft Visual FoxPro for Windows 3.0
Microsoft Visual FoxPro for Windows 3.0b
Microsoft Visual FoxPro for Windows 5.0
Microsoft Visual FoxPro for Windows 5.0a
Microsoft Visual FoxPro for Windows 6.0
Microsoft Visual FoxPro for Windows 7.0

This article was previously published under Q259274

SYMPTOMS

SQL-92 syntax supports the SELECT statement in the INSERT statement. This functionality is implemented in Microsoft SQL Server. However, this syntax is not supported in Microsoft Visual FoxPro 7.0 and earlier versions. In Visual FoxPro 8.0 and later versions, the INSERT-SQL command does support the SELECT-SQL statement.

RESOLUTION

The functionality of this syntax can be simulated with a SCAN loop, as follows:

USE HOME() + "labels"
COPY STRUCTURE TO labelsNew

SELECT type, id, name, readOnly, ckVal, data ;
   FROM labels ;
   INTO CURSOR crsrLabels

SELECT crsrLabels
SCAN
   INSERT INTO labelsNew ;
      VALUES ;
      (crsrLabels.type, crsrLabels.id, crsrLabels.name, ;
       crsrLabels.readOnly, crsrLabels.ckVal, crsrLabels.data, ;
       DATE())
ENDSCAN

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

The following code demonstrates the way it would work if the SQL-92 syntax were supported. As it is, the code results in a syntax error.

USE HOME() + "labels"
COPY STRUCTURE TO labelsNew

INSERT INTO labelsNew ;
   SELECT type, id, name, readOnly, ckVal, data, DATE() ;
      FROM labels

REFERENCES

For more information on the INSERT - SQL and SELECT - SQL commands, please see the Visual FoxPro documentation.

Modification Type:	Major	Last Reviewed:	3/11/2005
Keywords:	kbCodeSnippet kbDatabase kbprb kbSQLProg KB259274