Q259274: PRB: INSERT - SQL Does Not Support Use of SELECT Statement

Article: Q259274
Product(s): Microsoft FoxPro
Version(s): MACINTOSH:3.0b; WINDOWS:3.0,3.0b,5.0,5.0a,6.0
Operating System(s): 
Keyword(s): kbDatabase kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox kbD
Last Modified: 27-JUL-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual FoxPro for Macintosh, version 3.0b 
- Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

SQL-92 syntax supports the use of a SELECT statement in an INSERT statement, and
this functionality is implemented in Microsoft SQL Server. However, this syntax
is not supported in Visual FoxPro.

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.

Additional query words:

======================================================================
Keywords          : kbDatabase kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport kbCodeSnippet kbSQLProg 
Technology        : kbHWMAC kbOSMAC kbVFPsearch kbAudDeveloper kbVFP300bMac kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : MACINTOSH:3.0b; WINDOWS:3.0,3.0b,5.0,5.0a,6.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.