Databases Reference
In-Depth Information
INSERT INTO NEWBOOKS
SELECT ISBN, PubID, Price
FROM BOOKS
WHERE Price>20;
6.7.7.1 Note
Text field values must be enclosed in quotation marks.
6.7.8 The SELECT...INTO Statement
The SELECT... INTO statement is equivalent to a MakeTable query. It makes a new
table and inserts data from other tables. The syntax is:
SELECT
FieldName
,...
INTO
NewTableName
FROM
Source
WHERE
RowCondition
ORDER BY
OrderCondition
FieldName
is the name of the field to be copied into the new table.
Source
is the name of
the table from which data is taken. This can also be the name of a query or a join
statement.
For example, the following statement creates a new table called EXPENSIVEBOOKS
and includes books from the BOOKS table that cost more than $45.00:
SELECT Title, ISBN
INTO EXPENSIVEBOOKS
FROM BOOKS
WHERE Price>45
ORDER BY Title;
6.7.8.1 Notes
This statement is unique to Access SQL.
•
This statement does not create indexes in the new table.
•
6.7.9 TRANSFORM
The TRANSFORM statement (which is not part of SQL-92) is designed to create
crosstab queries. The basic syntax is:
TRANSFORM
AggregateFunction
SelectStatement
PIVOT
ColumnHeadingsColumn
[IN (Value,...)]
The
AggregateFunction
is one of Access' aggregate functions (
Avg
,
Count
,
Min
,
Max
,
Sum
,
First
,
Last
,
StDev
,
StDevP
,
Var
, and
VarP
). The
ColumnHeadingsColumn
is the