INTO Clause

In order to create a temporary table based on database data, set the temporary tables manager for the Query object, then run a database query using the INTO keyword followed by the temporary table name.

The INTO keyword is placed after the query selection list. For example:

SELECT
    Code,
    Description
INTO TemporaryTable
FROM Catalog.Products

The query result will contain rows with Code and Description columns that will include records stored into the created table.

If the temporary tables manager is not set or is closed or if the temporary tables manager already has the table with the specified name and the query is not batch-based, an error is generated.

In order to create a temporary table based on an external source, include the name of the parameter that will store the external source in the list of query sources. The rest of the syntax is identical to regular creation of a temporary table.

The following entities can be external sources:

• value table
• tabular section
• query result

An example of a temporary table creation based on an external source is shown below:

SELECT
    Code,
    Description
INTO TemporaryTable
FROM &ExternalSource AS ExternalSource

In this example the contents of the Code and Description columns are stored to the TemporaryTable from an external source, such as a value table passed as an ExternalSource parameter.

If the temporary table is created based on an external source, you are not allowed to use unions and joins in the query. You also cannot use fields that are attributes of table fields if these tables are used to create the temporary table.

If a value table is used as the source, it should have column value types explicitly specified.

Note. AUTOORDER, TOTALS, FOR UPDATE, HAVING and GROUP BY clauses cannot be used in a query that forms a temporary table based on a value table.

If you want to create a temporary table and lock data in the underlying tables, use the FOR UPDATE clause, as in the example.

SELECT
    ExpInvoice.Ref,
    ExpInvoice.Number,
    ExpInvoice.Date
INTO TemporaryTable
FROM
    Document.ExpInvoice AS ExpInvoice
WHERE
    ExpInvoice.Ref IN(&Documents)
FOR UPDATE