Share via

SELECT - SQL Command - INTO or TO Clause

  • 06/18/2008

The INTO or TO clauses specify output options that determine the location for storing or sending the final query results from a SQL SELECT statement.

For more information, see SELECT - SQL Command.

The detailed syntax for the INTO or TO clause is as follows:

[INTO StorageDestination | TO DisplayDestination ]

Parameters

  • [INTO StorageDestination]
    Stores the query results in an array, cursor, or table.

    Note

    If you do not include the INTO clause, query results are displayed in a Browse window by default. To direct the query results to the printer or a file, use the TO clause.

    The following table describes locations in which you can store the query results using the INTO clause.

    StorageDestination

    Description

    ARRAY ArrayName

    Stores query results in a memory variable array.

    Note

    If the query selects zero records, the array is not created.

    CURSOR CursorName [NOFILTER | READWRITE]

    Stores query results in a temporary cursor.

    > [!NOTE]
> <P>If you specify the name of an open table, Visual FoxPro generates an error message. After SELECT is executed, the temporary cursor remains open and is active and read-only unless you specify the READWRITE option. When you close this temporary cursor, it is deleted. Cursors can exist as a temporary file on the drive or volume specified by SORTWORK.</P>


<p>NOFILTER creates a cursor that can be used in subsequent queries.</p>

> [!NOTE]
> <P>Including NOFILTER can reduce query performance because it creates a temporary table on disk. When the cursor is closed, the temporary table is deleted from disk.</P>


<p>READWRITE specifies that the temporary cursor is modifiable. If the source table or tables use auto-incrementing, the cursor created with READWRITE does not inherit those settings. You can create more than one structural index on a cursor using the READWRITE argument.</p></td>
</tr>
<tr class="odd">
<td><p>TABLE TableName [DATABASE DatabaseName [NAME LongTableName]]</p></td>
<td><p>Stores query results in a table.</p>

> [!WARNING]
> <P>If you specify a table that is open, and SET SAFETY is set to OFF, Visual FoxPro overwrites the table without warning.</P>


<p>If you do not specify an extension, Visual FoxPro gives the table a .dbf extension. The table remains open and active after SELECT is executed.</p>
<p>DATABASE DatabaseName specifies a database you can add the table to.</p>
<p>NAME LongTableName specifies a long name for the table. Long names can contain up to 128 characters and can be used in place of short file names in the database.</p></td>
</tr>
</tbody>
</table>

    • [TO DisplayDestination]
      Sends the query results to a file, printer, main Visual FoxPro window, or an active user-defined window.

      Note

      Visual FoxPro disregards the TO clause if you also include the INTO clause in the same query.

      The following table describes locations that you can send the query results using the TO clause.

      DisplayDestination

      Description

      FILE FileName [ADDITIVE]

      Specifies an ASCII text file that you can direct the query results to.

      ADDITIVE appends query output to the existing contents of the text file specified by FileName.

      PRINTER [PROMPT]

      Directs query output to a printer.

      PROMPT displays a printer dialog box before printing begins. You can adjust printer settings in the dialog box. The printer settings that you can adjust depend on the currently installed printer driver.

      SCREEN

      Directs query output to the main Visual FoxPro window or an active user-defined window.

    Remarks

    The following code shows a summary of the main clauses of the SELECT - SQL Command:

    SELECT Select_List
   FROM Table_List
...[WITH (BUFFERING = lExpr)]
   [WHERE Conditions]
   [GROUP BY Column_List]
   [UNION Clause]
   [HAVING Conditions]
   [ORDER BY Column_List]
   [INTO Clause | TO Clause ] 
   [Additional_Display_Options]

    For more information about a particular clause of the SQL SELECT command, see the following topics:

    Example

    The following example stores the contents of a query into a table using INTO TABLE TableName clause. The example stores the contents of the Company, Order_Date, and Shipped_On fields from the Customer and Order tables, which are joined on the Cust_ID field, in a third table called CustShip. The example then opens a Browse window for the new table. The SELECT statement specifies local aliases for the tables to distinguish the same field name, Cust_ID, in both tables.

    CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.company, TAlias2.order_date, TAlias2.shipped_on ;
   FROM customer TAlias1, orders TAlias2 ;
   WHERE TAlias1.cust_id = TAlias2.cust_id ;
   INTO TABLE custship.dbf
BROWSE

    See Also

    Reference

    CREATE QUERY Command

    CREATE TABLE - SQL Command

    MODIFY QUERY Command

    Other Resources

    Working with Queries

    Working with Views (Visual FoxPro)

    Query and View Designers