SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 301

Further, you need not provide the values in the same order as the columns in the table. If
for some reason you want to provide Nationality first, you can revise the column names and
the data values, as shown in the following example:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-INSERT-CH07-03 *** */
INSERT INTO ARTIST

(Nationality, LastName, FirstName, DateOfBirth, DateDeceased)

VALUES ('Spanish', 'Miro', 'Joan', 1893, 1983);

If you have partial values, just code the names of the columns for which you have data. For
example, if you have only LastName, FirstName, and Nationality for an artist, you would use
the SQL statement:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-INSERT-CH07-04 *** */
INSERT INTO ARTIST

(LastName, FirstName, Nationality)

VALUES ('Miro', 'Joan', 'Spanish');

You must, of course, have values for all NOT NULL columns.

Bulk INSERT
One of the most often used forms of INSERT uses an SQL SELECT statement to provide values.
Suppose you have the names, nationalities, birth dates, and dates deceased of a number of art-
ists in a table named IMPORTED_ARTIST. In this case, you can add those data to the ARTIST
table with the following statement:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-INSERT-CH07-05 *** */
INSERT INTO ARTIST
(LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
SELECT LastName, FirstName, Nationality,

DateOfBirth, DateDeceased

FROM IMPORTED_ARTIST;

Note that the SQL keyword VALUES is not used with this form of insert. This syn-
tax should seem familiar. We used it for normalization and denormalization examples in
Chapters 3 and 4.

Populating the View Ridge Gallery Database Tables
Now that we know how to use the SQL INSERT statement to add rows of data to a table, we
can put data into the View Ridge Gallery database. Sample data for the View Ridge Gallery
Database is shown in Figure 7-16.
However, we need to be careful about exactly how we enter these data into the View
Ridge Gallery Database. Notice that in the SQL CREATE TABLE statements in Figure 7-14
CustomerID, ArtistID, WorkID, and TransactionID are all surrogate keys with values automati-
cally inserted by the DBMS. This will produce sequential numbers. For example, if we insert
the ARTIST table data shown in Figure 7-16(b) using the automatic ArtistID numbering from
IDENTITY(1, 1), the ArtistID numbers for the nine artists will be (1, 2, 3, 4, 5, 6, 7, 8, 9). But in
Figure 7-12(b), the ArtistID numbers are (1, 2, 3, 4, 5, 11, 17, 18, 19).