Create a Make-Table Query

Background information

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 Append query, but creates a new table to copy rows into.

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, you must have permission to create a table in the target database.

Create a Make-Table query

1. In the Database window, click Queries   under Objects, and then click New.
2. In the New Query dialog box, select Design Stored Procedure.
3. In the Add Table dialog box, add the tables, views, or functions you want to use to create the query, and then click Close.
4. On the Query menu click Make- Table Query.
5. 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 using the following syntax:

   database.owner.table

   For more information on qualified object names, see the SQL Server documentation.

6. Specify the columns to copy by adding them to the query. 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.

7. If you want to copy rows in a particular order, specify a sort order.
8. Specify the rows to copy by entering search conditions.

   If you do not specify a search condition, all rows 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 rectangle representing the table, view, or function ().

9. If you want to copy summary information, specify Group By options.

When you execute a Make-Table query by using the Run button on the Query Designer toolbar, a message appears indicating how many rows were copied.