DOCUMENT:Q259274  27-JUL-2001  [foxpro]
TITLE   :PRB: INSERT - SQL Does Not Support Use of SELECT Statement
PRODUCT :Microsoft FoxPro
PROD/VER:MACINTOSH:3.0b; WINDOWS:3.0,3.0b,5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:kbDatabase kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox kbD

======================================================================
-------------------------------------------------------------------------------
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 2001.