Show
Saving a Query Result in a TableProblemYou want to save the result from a SolutionIf
the table already exists, just use DiscussionThe MySQL server normally returns the result of a
This section shows how to retrieve a result set into a table. The table names If the destination table already exists, use INSERT INTO dst_tbl (i, s) SELECT val, name FROM src_tbl; The number of columns to be inserted must match the number of selected columns, and the correspondence between sets of columns is established by position rather than name. In the special case that you want to copy all columns from one table to another, you can shorten the statement to this form: INSERT INTO dst_tbl SELECT * FROM src_tbl; To copy only certain rows, add
a INSERT INTO dst_tbl SELECT * FROM src_tbl WHERE val > 100 AND name LIKE 'A%'; The INSERT INTO dst_tbl (i, s) SELECT COUNT(*), name FROM src_tbl GROUP BY name; If the destination table does not exist, you can create it first with a CREATE TABLE dst_tbl SELECT * FROM src_tbl; MySQL creates the columns in CREATE TABLE dst_tbl SELECT * FROM src_tbl WHERE 0; To copy
only some of the columns, name the ones you want in the CREATE TABLE dst_tbl SELECT b, d FROM src_tbl; To create columns in a different order from that in which they appear in the source table, name them in the desired order. If the source table contains columns CREATE TABLE dst_tbl SELECT c, a, b FROM src_tbl; To create additional columns in the destination table besides those selected from the source table, provide appropriate column definitions in the CREATE TABLE dst_tbl ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT a, b, c FROM src_tbl; The resulting table contains four columns in the order If you derive a column’s values from an expression, it’s prudent to provide an alias
to give the column a name. Suppose that CREATE TABLE dst_tbl SELECT inv_no, SUM(unit_cost*quantity) AS total_cost FROM src_tbl GROUP BY inv_no;
When a user saves a new query the results of the query are saved in the database for future reference True or false?When a user saves a new query, the results of the query are saved in the database for future reference. To create a crosstab query using fields from more than one table, the user must create a select query with the fields from both tables and then use the query as the data source for the crosstab query.
What type of action query allows you to add the results of a query to a new table?You use an append query when you need to add new records to an existing table by using data from other sources. If you need to change data in an existing set of records, such as updating the value of a field, you can use an update query.
When creating a new table Access creates the first field and names it?When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of ID and the AutoNumber data type. In Design view, you can change or remove the primary key, or set the primary key for a table that doesn't already have one.
Is a database object that retrieves specific data from one or more database objects?A query is a database object that retrieves specific data from one or more database objects—either tables of other queries. A form is an Access object with which you can enter data, edit data, or display data from a table or a query.
|