A Make Table query is similar to an Insert Results query but creates a new table to copy rows into. Developers 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. When developers create a Make Table query, they specify:
– The name of the destination table.
– The source table or tables to copy rows from. Developers can copy from a single table or from joined tables.
– The columns in the source table whose contents they want to copy.
– Sort order, if they want to copy the rows in a particular order.
– Search conditions to define the rows they want to copy.
– Group By options, if they want to copy only summary information.
For example, the following query creates a new table called us_clients and copies information from the clients table to it:
SELECT *
INTO us_clients
FROM clients
WHERE country = ‘USA’
In order to use a Make Table query successfully:
– Developers’ database must support the SELECT…INTO syntax.
– They must have permission to create a table in the target database.
When developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to create an Insert values queries, they should follow the next steps:
1. Add the source table or tables to the Diagram pane.
2. From the Query Designer menu, point to Change Type, and then click Make Table.
3. In the Make Table dialog box, type the name of the destination table. The Query and View Designer does not check whether the name is already in use or whether they 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 How to: Add Columns to Queries (Visual Database Tools). Columns will be copied only if they add them to the query. To copy entire rows, choose * (All Columns).
The Query and View Designer adds the columns they choose to the Column column of the Criteria pane.
5. If they want to copy rows in a particular order, specify a sort order.
6. Specify the rows to copy by entering search conditions.
If they do not specify a search condition, all rows from the source table will be copied to the destination table. When they add a column to search to the Criteria pane, the Query and View Designer also adds it to the list of columns to copy. If they 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 or table-structured object.
7. If they want to copy summary information, specify Group By options.
When developers execute a Make Table query, no results are reported in the Results Pane. Instead, a message appears indicating how many rows were copied.