You can copy rows from one table to another or within a table using an Append query. For example, in a titles table, you can use an Append query to copy information about all the titles for one publisher to a second table that you can make available to that publisher. An Append query is similar to a Make-Table query, but copies rows into an existing table.
When you create an Append 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 Append Values 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 Append query, the Grid pane changes to reflect options available for copying data. Because you do not display data in an Append query, the Output column is removed. An Append column is added to allow you to specify the columns into which data should be copied.
Create an Append query- In the Database window, click Queries
under Objects, and then click New. - In the New Query dialog box, select Design Stored Procedure.
- In the Add Table dialog box, add the tables, views, or functions you want to use to create the Append query, and then click Close.
- On the Query menu click Append Query.
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 Results 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.
- Add to the query the table to copy rows from (the source table). If you are copying rows within a table, you can add the source table as a destination table.
- In the rectangle representing the table, view, or function, 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.
- 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.
- If you want to copy rows in a particular order, specify a sort order.
- Specify the rows to copy by entering search conditions in the Criteria column.
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 (
). - If you want to copy summary information, specify Group By options.
When you execute an Append query by using the Run
button on the Query Designer toolbar, a message appears indicating how many rows were copied.