Datasheet

This may be simple, but there are several points to note:
❑ The
INTO keyword is optional, but it makes the query easier to read. If you want, however, you
can omit it from your queries.
❑
The list of columns specified may be a subset of the columns defined in the table for several reasons:
❑ Some columns may allow null values, in which case adding data to them is optional.
❑ Some columns may be defined with default values, in which case the default will be
used if the column is omitted from the insert statement.
❑ A column may be defined as an identity column, in which case the RDBMS may not allow
you to insert data into it, and will generate an error if you attempt to do so. This is because
identity columns are maintained by the RDBMS, and will be assigned values automatically.
❑ The columns don’t have to be specified in the same order as they are defined in the table. The
important thing to remember is that the column order specified in the insert statement must
match the order of the values specified in the statement.
❑ Other rules applying to columns also apply, such as the rule that primary key values must be
unique. The preceding query will execute fine once, but attempting to execute it again will
result in an error because you will be attempting to insert duplicate values into
ProductCategoryId.
❑ Null values can be inserted into columns using the keyword
NULL.
For example, the following would be legal syntax for adding a row to the
PhoneBookEntry described
earlier in this chapter:
INSERT INTO PhoneBookEntry (EntryName, PhoneNumber, Address, IsIndividual)
VALUES (‘Wayne Rooney’, ‘555 123456’, ‘c/o Sven’, 1)
The following would cause an error:
INSERT INTO PhoneBookEntry (EntryName, PhoneNumber, Address, IsIndividual)
VALUES (‘Wayne Rooney’, ‘555 123456’, 1, ‘c/o Sven’)
In this example, the order of values supplied is wrong — it doesn’t match the order specified by the col-
umn names.
This is the ideal place for a handy tip, and also the first time you will see a function in
action. When using the
uniqueidentifier type in SQL Server, which, as you have
already seen, is a GUID value, you can use the
NEWID() function to generate a GUID
value. For example:
INSERT INTO ProductCategory (ProductCategoryId, CategoryName)
VALUES (NEWID(), ‘Doodads’)
When this statement is executed, the NEWID() function is called and returns a GUID
value that is used in the new row — meaning that you don’t have to generate a GUID
externally. Another practical function when adding rows is
GETDATE(), which obtains
the current date and time — useful when date-stamping rows. In SQL Server, you can
use the
datetime column data type for this purpose.
26
Chapter 1
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 26