INSERT, UPDATE, AND DELETE REVISITED
While the SELECT statement seems to draw all the attention of the end user, developers responsible for implementing business logic for the applications hold INSERT, UPDATE, and DELETE statements in equal respect. After all, they are concerned with getting the data in, managing it there as long as needed, and retiring it when the need is gone. Proper use of the statements is the hallmarks of a well-behaved database.
The classic INSERT requires you to list all columns in the table and supply corresponding values for each column. For instance, the full insert into the BOOKS table might look like this:
INSERT INTO books (bk_id ‚bk_title ‚bk_ISBN ‚bk_publisher ‚bk_published_year ‚bk_price ‚bk_page_count ‚bk_bought_on ‚bk_hard_cover ‚bk_cover_pic ‚bk_notes) VALUES ( 1 ‚‘SQL Bible’ ‚‘978-0470229064’ ‚‘Wiley’ ‚2008 ‚39.99 ‚888 ‚CAST(‘10-10-2009’ as smalldatetime) ‚0 ‚NULL ‚NULL)
This is fairly intuitive: a list of columns (in any order), and a matching list of values (in matching order), formatted for appropriate data types (string, numbers, dates), and off we go. The shortened version of the same statement would get rid of the columns list:
INSERT INTO books VALUES ( 1 ‚‘SQL Bible’ ‚‘978-0470229064’ ‚‘Wiley’ ‚2008 ‚39.99 ‚888 ‚CAST(‘10-10-2009’ as smalldatetime) ‚0 ‚NULL ‚NULL)
While handy, this syntax imposes two major restrictions: ...