How to create an Insert query (Enterprise Manager)

To create an Insert query

1. Start an Insert query by right-clicking in the Diagram or SQL pane, choosing Change Type, and then choosing Insert.
2. In the Insert Into Table 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 Insert Into Table 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 Input Sources. 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 input source window, 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 input source window.

   ---

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