Creating Insert From Queries

  • Article

You can copy rows from one table to another or within a table using an Insert From query. For example, in a titles table, you can use an Insert From query to copy information about all the titles for one publisher to a second table that you can make available to that publisher. An Insert From query is similar to a Make Table query, but copies rows into an existing table.

Tip   You can also copy rows from one table to another using cut and paste. For details, see Adding New Rows in the Results Pane.

When you create an Insert From query, you specify:

  • The database table to copy rows to (the destination table).

  • The table or tables to copy rows from (the source table). The source table or tables become part of a subquery . If you are copying within a table, the source table is the same as the destination table.

  • The columns in the source table whose contents you want to copy.

  • The target columns in the destination table to copy the data to.

  • Search conditions to define the rows you want to copy.

  • Sort order, if you want to copy the rows in a particular order.

  • Group By options, if you want to copy only summary information.

For example, the following query copies title information from the titles table to an archive table called archivetitles. The query copies the contents of four columns for all titles belonging to a particular publisher:

  INSERT INTO archivetitles 
   (title_id, title, type, pub_id)
SELECT title_id, title, type, pub_id
FROM titles
WHERE (pub_id = '0766')

Note   To insert values into a new row, use an Insert Into query.

You can copy the contents of selected columns or of all columns in a row. In either case, the data you are copying must be compatible with the columns in the rows you are copying to. For example, if you copy the contents of a column such as price, the column in the row you are copying to must accept numeric data with decimal places. If you are copying an entire row, the destination table must have compatible columns in the same physical position as the source table.

When you create an Insert From query, the Grid pane changes to reflect options available for copying data. An Append column is added to allow you to specify the columns into which data should be copied.

Caution   You cannot undo the action of executing an Insert From query. As a precaution, back up your data before executing the query.

To create an Insert From query

  1. Right-click in the Query Designer window, point to Change Type, and then choose Insert From.

  2. In the Choose Table for INSERT FROM Query dialog box, select the table to copy rows to (the destination table).

    Note   The Query Designer cannot determine in advance which tables and views you can update. Therefore, the Table Name list in the Choose Table for Insert From Query dialog box shows all available tables and views in the data connection you are querying, even those that you might not be able to copy rows to.

  3. Add to the query the table to copy rows from (the source table). For details, see Adding Tables. If you are copying rows within a table, you can add the source table as a destination table.
    The data columns from the source table appear in an input window in the Diagram pane.

  4. In the rectangle representing the table or table-structured object, choose the names of the columns whose contents you want to copy. To copy entire rows, choose * (All Columns).

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

  5. In the Append column of the Grid pane, select a target column in the destination table for each column you are copying. Choose tablename.* if you are copying entire rows. The columns in the destination table must have the same (or compatible) data types as the columns in the source table.

  6. If you want to copy rows in a particular order, specify a sort order. For details, see Ordering Query Results.

  7. Specify the rows to copy by entering search conditions in the Criteria column. 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 rectangle representing the table or table-structured object

  1. 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 an Insert From query, no results are reported in the Results pane. Instead, a message appears indicating how many rows were copied.

See Also

Creating Delete Queries | Creating Update Queries | Supported Query Types