INSERTing Data
11/10/2000In the last two columns we've focused on sorting and filtering data that is already stored in a database using the SELECT statement. But how did it get there in the first place? If you use some sort of GUI like Microsoft Access, TOAD for Oracle, or even the PHP-based phpMyAdmin tool, you just enter data directly into the database and never have to think about it again. Or you may get the data into the database using some sort of import facility of your database software. But doesn't SQL have the ability to directly enter data into the database? Of course it does!
The INSERT statement
We've done a lot of data manipulation using the SELECT statement on data that already exists in the database. If we want to put data into the database, however, the verb we need to use is (not surprisingly) INSERT. The most basic way to do a data INSERT is to add an entire row of data to the database. The syntax is:
INSERT INTO table_name VALUES(list_of_values);
|
Previously in aboutSQL: • AboutSQL: Filtering SELECTed Data with WHERE • AboutSQL: Introducing SELECT • What's the Big Deal about SQL?  
|
So to add a new album to our basic music database from the previous examples, the syntax would be
INSERT INTO MusicCollection VALUES(5,'Supernatural','Santana',1999);
which makes the database table look something like this (using SELECT * FROM MusicCollection perhaps?):
|
MusicCollection
|
|||
| ID | Title | Artist | Year |
| 1 | Pet Sounds | The Beach Boys | 1966 |
| 2 | Security | Peter Gabriel | 1990 |
| 3 | The Way it Is | Bruce Hornsby | 1986 |
| 4 | Joshua Judges Ruth | Lyle Lovett | 1992 |
| 5 | Supernatural | Santana | 1999 |
Not too complicated, but there are a number of new features that were thrown at you in that simple INSERT command -- there are all the syntactical elements mentioned above (verbs, clauses, etc.), but note how the data was formatted in the list of values. There a few crucial things to note about the INSERT command:
- There must be exactly as many values as there are fields in the database, and
- text values always use single quotes!
It is also a good idea to make sure the data is in the proper order, though the database doesn't care as long as the types of fields are correct. For example, you can assume the the ID and Year fields are numeric data while both the Title and Artist fields are simple textual data. This means that the INSERT statement will only work if the data types of the parameters in VALUE clause contains (numeric, text, text, numeric). The database would be incorrect if you reversed Artist and Title in your VALUE clause, but the SQL statment would still work. However, you'd get a SQL error if you reverse ID and Title since they are of different data types. (We'll discuss data types in depth in another column.)
If you need to actually insert a single quote, you can escape it (as in many programming languages) by typing two of them. So to insert the new album by Sinead O'Conner, you could use this statement:
INSERT INTO MusicCollection VALUES(6,'Faith & Courage','Sinead O''Conner',2000);
which adds this line to the MusicCollection table:
| 6 | Faith & Courage | Sinead O'Conner | 2000 |
where the "escaped" single quote is properly entered.
INSERTs the right way
While the syntax we've looked at for the INSERT command is straightforward, it has one fundamental flaw -- your SQL code is directly linked to the order of fields in the database table. If a new field is inserted, your code suddenly breaks! A much better way to handle INSERTs is to fully specify the relationship between fieldnames and values in the SQL statement itself:
INSERT INTO table_name(list_of_fields) VALUES(list_of_values);
The proper way to INSERT the Santana CD would be:
INSERT INTO MusicCollection(ID,Title,Artist,Year) VALUES(5,'Supernatural','Santana',1999);
In this syntax, the contents of the list of values are mapped in order to the list of fieldnames specified after MusicCollection. The big advantage is that now the order of the database is independent of the SQL code. For example, we could also insert the Santana CD using this syntax
INSERT INTO MusicCollection(Title,ID,Year,Artist) VALUES('Supernatural',5,1999,'Santana');
which would produce the same result as the previous statement. If we tried it without specifying the fields, you'd get a SQL error since the database thinks the fields in MusicCollection (in order) are ID, Title, Artist, Year.
One added advantage of doing INSERTs in this manner is that you can add a partial record to the database table. If the field in a database table can either
- have a value of NULL (no value) or
- provide a default value
then you can omit that field. These field details are controlled in the definition of the database table, which is far beyond the scope of today's column. Just assume for the moment that the database administrator (DBA) has added a new field to the MusicCollection database called Review which ranks the album on a scale of 1 to 10. If the DBA allows NULL values in the field, the SQL statement
INSERT INTO MusicCollection(Title,ID,Year,Artist) VALUES('Supernatural',5,1999,'Santana');
would work fine, but result in this entry in the database:
| ID | Title | Artist | Year | Review |
| 5 | Supernatural | Santana | 1999 | NULL |
If Review was not allowed to have NULL values (and thus was effectively a required field), then that SQL statement would generate an error. Also note that our earlier SQL statement that did not specify the field names would break, since it provided four field values and the database now has five fields.
Next steps
This week we took a big step and added the SQL INSERT statement to our bag of tricks. The syntax is:
INSERT INTO table_name[(field_names)] VALUES(field_values);
where the (field_names) parameter is optional but highly encouraged.
Next time, we'll take the next logical step and see how to change data once it's in the database. Until then, you've got the INSERT and SELECT statements to work with, now that you're starting to know a bit aboutSQL.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Discuss this article in the O'Reilly Network Linux Forum.
Return to the Linux DevCenter.
