Create a query (ADP)

You can create a new row in the current table using an Append Values query. When you create an Append Values query, you specify:

• The database table to add the row to.
• The columns whose contents you want to add.
• The value or expression to insert into the individual columns.

For example, the following query adds a row to the titles table, specifying values for the title, type, publisher, and price:

INSERT INTO titles
 (title_id, title, type, pub_id, price)
VALUES ('BU9876', 'Creating Web Pages', 'business', '1389', '29.99')
								

When you create an Append Values query, the Grid pane changes to reflect the only options available for inserting a new row: the column name and the value to insert.

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 table, view, or function you want to append values to, and then click Close.
4. On the Query menu click Append Values Query.

   Note   If more than one table, view, or function is displayed in the Grid pane when you start the Append Values query, the Query Designer displays the Choose Target Table for Insert Values dialog box to prompt you for the name of the table to update.

5. Define the data columns to create by adding them to the query. Columns will be updated only if you add them to the query.
6. In the New Value column of the Grid pane, enter the new value for the column. You can enter literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column you are updating.

   Caution   The Query Designer cannot check that a value fits within the length of the column you are inserting. If you provide a value that is too long, it might be truncated without warning. For example, if a name column is 20 characters long but you specify an insert value of 25 characters, the last 5 characters might be truncated.

When you execute an Append Values query by using the Run button on the Query Designer toolbar, a message appears indicating how many rows were changed.

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.

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 Append query, and then click Close.
4. 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.

5. 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.
6. 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.

7. 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.
8. If you want to copy rows in a particular order, specify a sort order.
9. 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 ( ).

10. 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.

You can change the contents of multiple rows in one operation by using an Update query. For example, in a titles table you can use an Update query to add 10% to the price of all books for a particular publisher.

When you create an Update query, you specify:

• The table to update.
• The columns whose contents you want to update.
• The value or expression to use to update the individual columns.
• Search conditions to define the rows you want to update.

For example, the following query updates the titles table by adding 10% to the price of all titles for one publisher:

UPDATE titles
SET price = price * 1.1
WHERE (pub_id = '0766')
								

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 table, view, or function you want to use to update, and then click Close.
4. On the Query menu click Append Query.

   Note  If more than one table is displayed in the Grid pane when you start the Update query, the Query Designer displays the Update Table dialog box to prompt you for the name of the table to update.

5. Define the data columns to update by adding them to the query. Columns will be updated only if you add them to the query.
6. In the New Value column of the Grid pane, enter the update value for the column. You can enter literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column you are updating.

   Caution   The Query Designer cannot check that a value fits within the length of the column you are updating. If you provide a value that is too long, it might be truncated without warning. For example, if a name column is 20 characters long but you specify an update value of 25 characters, the last 5 characters might be truncated.

7. Define the rows to update by entering search conditions in the Criteria column.

   If you do not specify a search condition, all rows will be updated.

   Note   When you add a column to the Grid pane for use in a search condition, the Query Designer also adds it to the list of columns to be updated. If you want to use a column for a search condition but not update it, clear the check box next to the column name in the rectangle representing the table, view, or function . ( ).

When you execute an Update query by using the Run button on the Query Designer toolbar, a message appears indicating how many rows were changed.

You can delete multiple rows in one operation by using a Delete query. When you create a Delete query, you specify the database table to delete rows from and the search condition to define the rows you want to delete.

Note  Deleting all rows from a table clears the data in the table but does not delete the table itself. You can delete tables using the Database Designer.

When you create a Delete query, the Grid pane changes to reflect the options available for deleting rows. Because you do not display data in a Delete query, the Output, Sort By, and Sort Order columns are removed. In addition, the check boxes next to the column names in the rectangle representing the table, view, or function are removed because you cannot specify individual columns to delete.

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 in the delete query, and then click Close.
4. On the Query menu click Delete Query.
5. Enter search conditions to define the rows to be deleted. If you do not specify a search condition, all rows will be deleted.

   Note   Because you cannot delete the contents of individual columns, the check boxes in the rectangle representing the table, view, or function are removed when you start a Delete query. To add columns to the Grid pane to use in search conditions, drag them from the rectangle representing the table, view, or function to the Columns column, or select a data column in the Columns column.

When you execute the Delete query by using the Run button on the Query Designer toolbar, a message appears indicating how many rows were deleted.

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.

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.