Creating Make Table Queries

See Also

You can copy rows into a new table using a Make Table query, which is useful for creating subsets of data to work with or copying the contents of a table from one database to another. A Make Table query is similar to an Insert query, but creates a new table to copy rows into.

Note   In Oracle, a Make Table query is implemented as a Create Table As Select command. For details, see Query Designer Considerations for Oracle Databases.

When you create a Make Table query, you specify:

• The name of the new database table (the destination table).
  
  
• The table or tables to copy rows from (the source table). You can copy from a single table or from joined tables.
  
  
• The columns in the source table whose contents you want to copy.
  
  
• Sort order, if you want to copy the rows in a particular order.
  
  
• Search conditions to define the rows you want to copy.
  
  
• Group By options, if you want to copy only summary information.

For example, the following query creates a new table called uk_customers and copies information from the customers table to it:

SELECT * 
INTO uk_customers
FROM customers
WHERE country = 'UK'

In order to use a Make Table query successfully:

• Your database must support the SELECT...INTO syntax.
  
  
• You must have permission to create a table in the target database.

To create a Make Table query

1. Add the source table or tables to the Diagram pane.
   
   
2. Convert the default query to a Make Table query by choosing Change Type from the Query menu or the Query Designer Toolbar, and then choosing Make Table.
   
   
3. In the Make Table dialog box, type the name of the destination table. The Query Designer does not check whether the name is already in use or whether you have permission to create the table.

   

   To create a destination table in another database, specify a fully qualified table name including the name of the target database, the owner (if required), and the name of the table.

4. Specify the columns to copy by adding them to the query. For details, see Adding Columns. Columns will be copied only if you add them to the query. To copy entire rows, choose * (All Columns).

   

   The Query Designer adds the columns you choose to the Column column of the Grid pane.

5. If you want to copy rows in a particular order, specify a sort order. For details, see Ordering Query Results.
   
   
6. Specify the rows to copy by entering search conditions. For details, see Specifying Search Conditions.

   

   If you do not specify a search condition, all rows from the source table will be copied to the destination table.

   Note   When you add a column to search to the Grid pane, the Query Designer also adds it to the list of columns to copy. If you want to use a column for searching but not copy it, clear the check box next to the column name in the input source window ().

7. If you want to copy summary information, specify Group By options. For details, see Summarizing Values for All Rows in a Table.

When you execute a Make Table query, no results are reported in the Results pane. Instead, a message appears indicating how many rows were copied.