Datasheet

The INSERT keyword can also be used to insert multiple columns into a table at the same time. However,
this cannot be achieved by specifying all of the column values as part of the statement. Instead, you
must specify column values indirectly by using a SQL statement that returns a set of data — for exam-
ple, a select query.
In insert statements of this form you must omit the
VALUES keyword. The following is an example of a
multirow insert query:
INSERT INTO ProductW (ProductId, ProductName, ProductCost, ProductCategoryId)
SELECT ProductId, ProductName, ProductCost, ProductCategoryId FROM Product
WHERE ProductName LIKE ‘w%‘
This statement copies all the products from the Product table whose ProductName column value starts
with the letter
w into a table called ProductW — which has exactly the same column specification as
Product.
The query used to obtain data for inserting multiple rows needn’t be this simple. The data could come
from multiple tables, and the source columns needn’t have the same names as the destination columns
as long as their types match.
As with select statements, there is a lot more that you can do with insert statements, but these are the
basics that you need to add data to database tables using SQL.
Deleting Data
You can delete data from databases using the DELETE keyword. But first, a word of warning — the
delete statement makes it easy to accidentally delete the entire contents of a database table.
The syntax for a delete statement is as follows:
DELETE FROM [Table] WHERE [Filter]
Here, [Table] is the table from which to delete data, and [Filter] is a filter used to identify the data
to delete. Delete statements operate on whole rows of data — it is not possible to delete individual
columns from rows. The
FROM keyword is optional (like the INTO keyword in insert statements, it can be
more readable to leave it in), and the where clause is also optional.
If the where clause is omitted, all the rows in the table will be deleted. If you want this to happen, fine. If
not, then be careful!
The following statement deletes all rows from the
Product table:
DELETE FROM Product
As you can no doubt tell, this is a common mistake to make, and a serious one.
Using a filter, however, means that you can delete single records or a lot of records at once, depending
on what you want to do. For example:
DELETE FROM ProductA WHERE ProductName NOT LIKE ‘a%‘
27
Database Fundamentals
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 27