Most commercial relational database systems also provide tools for importing and exporting data. This data is traditionally stored in a delimited text file format. Often a format file is stored that contains information about the table being imported. Tools such as Oracle's SQL*Loader, SQL Server's bcp (bulk copy), and Microsoft Access Import/Export are covered at the end of the day.
NOTE: Today's examples were generated with Personal Oracle7. Please note the minor differences in the appearance of commands and the way data is displayed in the various implementations.
INSERT...VALUESand
INSERT...SELECT
INSERT INTO table_name (col1, col2...) VALUES(value1, value2...)The basic format of the INSERT...VALUES statement adds a record to a table using the columns you give it and the corresponding values you instruct it to add. You must follow three rules when inserting data into a table with the INSERT...VALUES statement:
SQL> SELECT * FROM COLLECTION;which would yield this:
ITEM WORTH REMARKS -------------------- --------- ---------------------------- NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES MALIBU BARBIE 150 TAN NEEDS WORK STAR WARS GLASS 5.5 HANDLE CHIPPED LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YETIf you wanted to add a new record to this table, you would write
SQL> INSERT INTO COLLECTION 2 (ITEM, WORTH, REMARKS) 3 VALUES('SUPERMANS CAPE', 250.00, 'TUGGED ON IT'); 1 row created.You can execute a simple SELECT statement to verify the insertion:
SQL> SELECT * FROM COLLECTION; ITEM WORTH REMARKS -------------------- --------- ---------------------------- NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES MALIBU BARBIE 150 TAN NEEDS WORK STAR WARS GLASS 5.5 HANDLE CHIPPED LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YET SUPERMANS CAPE 250 TUGGED ON IT
SQL> INSERT INTO COLLECTION VALUES 2 ('STRING',1000.00,'SOME DAY IT WILL BE VALUABLE');
1 row created.
SQL> SELECT * FROM COLLECTION;
ITEM WORTH REMARKS -------------------- --------- ---------------------------- NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES MALIBU BARBIE 150 TAN NEEDS WORK STAR WARS GLASS 5.5 HANDLE CHIPPED LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YET SUPERMANS CAPE 250 TUGGED ON IT STRING 1000 SOME DAY IT WILL BE VALUABLE 6 rows selected.
WARNING: You could insert spaces for a null column, but these spaces will be treated as a value. NULL simply means nothing is there.
SQL> insert into collection values 2 ('SPORES MILDEW FUNGUS', 50.00, ' ');
1 row inserted.
SQL> select * from collection 2 where remarks = ' '; ITEM WORTH REMARKS --------------------------- -------- --------- SPORES MILDEW FUNGUS 50.00 1 row selected.
Assume the column REMARKS in the preceding table has been defined as NOT NULL. Typing
SQL> INSERT INTO COLLECTION 2 VALUES('SPORES MILDEW FUNGUS',50.00,NULL);produces the following error:
INSERT INTO COLLECTION * ERROR at line 1: ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
NOTE: Notice the syntax. Number data types do not require quotes; NULL does not require quotes; character data types do require quotes.
SQL> INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING');
INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING') * ERROR at line 1: ORA-00001: unique constraint (PERKINS.UNQ_COLLECTION_ITEM) violated
IF NOT EXISTS (SELECT * FROM COLLECTION WHERE NAME = 'STRING' INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')This particular example is supported in the Sybase system.
A properly normalized table should have a unique, or key, field. This field is useful for joining data between tables, and it often improves the speed of your queries when using indexes. (See Day 10, "Creating Views and Indexes.")
NOTE: Here's an INSERT statement that inserts a new employee into a table:SQL> insert into employee_tbl values ('300500177', 'SMITHH', 'JOHN'); 1 row inserted.After hitting Enter, you noticed that you misspelled SMITH. Not to fret! All you have to do is issue the ROLLBACK command, and the row will not be inserted. See Day 11, "Controlling Transactions," for more on the ROLLBACK command.
Many database systems also support temporary tables. (See Day 14, "Dynamic Uses of SQL.") Temporary tables exist for the life of your database connection and are deleted when your connection is terminated. The INSERT...SELECT statement can take the output of a SELECT statement and insert these values into a temporary table.
Here is an example:
SQL> insert into tmp_tbl 2 select * from table;
19,999 rows inserted.
The syntax of the INSERT...SELECT statement is as follows:
NOTE: Not all database management systems support temporary tables. Check the documentation for the specific system you are using to determine if this feature is supported. Also, see Day 14 for a more detailed treatment of this topic.
INSERT INTO table_name (col1, col2...) SELECT col1, col2... FROM tablename WHERE search_conditionEssentially, the output of a standard SELECT query is then input into a database table. The same rules that applied to the INSERT...VALUES statement apply to the INSERT...SELECT statement. To copy the contents of the COLLECTION table into a new table called INVENTORY, execute the set of statements in Example 8.3.
SQL> CREATE TABLE INVENTORY 2 (ITEM CHAR(20), 3 COST NUMBER, 4 ROOM CHAR(20), 5 REMARKS CHAR(40));
Table created.The following INSERT fills the new INVENTORY table with data from COLLECTION.
SQL> INSERT INTO INVENTORY (ITEM, COST, REMARKS) 2 SELECT ITEM, WORTH, REMARKS 3 FROM COLLECTION; 6 rows created.You can verify that the INSERT works with this SELECT statement:
SQL> SELECT * FROM INVENTORY; ITEM COST ROOM REMARKS -------------------- --------- -------- ---------------------------- NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES MALIBU BARBIE 150 TAN NEEDS WORK STAR WARS GLASS 5.5 HANDLE CHIPPED LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YET SUPERMANS CAPE 250 TUGGED ON IT STRING 1000 SOME DAY IT WILL BE VALUABLE 6 rows selected.
NOTE: The data appears to be in the table; however, the transaction is not finalized until a COMMIT is issued. The transaction can be committed either by issuing the COMMIT command or by simply exiting. See Day 11 for more on the COMMIT command.
The INSERT...SELECT statement requires you to follow several new rules:
SQL> insert into copy_table 2 select * from original_table;Now you can make changes to the original table with a clear conscience.
NOTE: Later today you learn how to input data into a table using data from another database format. Nearly all businesses use a variety of database formats to store data for their organizations. The applications programmer is often expected to convert these formats, and you will learn some common methods for doing just that.
UPDATE table_name SET columnname1 = value1 [, columname2 = value2]... WHERE search_conditionThis statement checks the WHERE clause first. For all records in the given table in which the WHERE clause evaluates to TRUE, the corresponding value is updated.
SQL> UPDATE COLLECTION 2 SET WORTH = 900 3 WHERE ITEM = 'STRING';
1 row updated.To confirm the change, the query
SQL> SELECT * FROM COLLECTION 2 WHERE ITEM = 'STRING';yields
ITEM WORTH REMARKS -------------------- --------- ------------------------------ STRING 900 SOME DAY IT WILL BE VALUABLEHere is a multiple-column update:
SQL> update collection 2 set worth = 900, item = ball 3 where item = 'STRING'; 1 row updated.
NOTE: Your implementation might use a different syntax for multiple-row updates.
NOTE: Notice in the set that 900 does not have quotes, because it is a numeric data type. On the other hand, String is a character data type, which requires the quotes.
SQL> UPDATE COLLECTION 2 SET WORTH = 555; 6 rows updated.Performing a SELECT query shows that every record in the database was updated with that value:
SQL> SELECT * FROM COLLECTION; ITEM WORTH REMARKS -------------------- --------- ------------------------------ NBA ALL STAR CARDS 555 SOME STILL IN BIKE SPOKES MALIBU BARBIE 555 TAN NEEDS WORK STAR WARS GLASS 555 HANDLE CHIPPED LOCK OF SPOUSES HAIR 555 HASN'T NOTICED BALD SPOT YET SUPERMANS CAPE 555 TUGGED ON IT STRING 555 SOME DAY IT WILL BE VALUABLE 6 rows selected.You, of course, should check whether the column you are updating allows unique values only.
Some database systems provide an extension to the standard UPDATE syntax. SQL Server's Transact-SQL language, for instance, enables programmers to update the contents of a table based on the contents of several other tables by using a FROM clause. The extended syntax looks like this:
WARNING: If you omit the WHERE clause from the UPDATE statement, all records in the given table are updated.
UPDATE table_name SET columnname1 = value1 [, columname2 = value2]... FROM table_list WHERE search_condition
SQL> UPDATE COLLECTION 2 SET WORTH = WORTH * 0.005;that changes the table to this:
SQL> SELECT * FROM COLLECTION; ITEM WORTH REMARKS -------------------- -------- ---------------------------- NBA ALL STAR CARDS 2.775 SOME STILL IN BIKE SPOKES MALIBU BARBIE 2.775 TAN NEEDS WORK STAR WARS GLASS 2.775 HANDLE CHIPPED LOCK OF SPOUSES HAIR 2.775 HASN'T NOTICED BALD SPOT YET SUPERMANS CAPE 2.775 TUGGED ON IT STRING 2.775 SOME DAY IT WILL BE VALUABLE 6 rows selected.
The UPDATE statement can also update columns based on the result of an arithmetic expression. When using this technique, remember the requirement that the data type of the result of the expression must be the same as the data type of the field that is being modified. Also, the size of the value must fit within the size of the field that is being modified.
Two problems can result from the use of calculated values: truncation and overflow. Truncation results when the database system converts a fractional number to an integer, for instance. Overflow results when the resulting value is larger than the capacity of the modified column, which will cause an error to be returned by your database system.
NOTE: Some database systems handle the overflow problem for you. Oracle7 converts the number to exponential notation and presents the number that way. You should keep this potential error in mind when using number data types.
TIP: If you update a column(s) and notice an error after you run the update, issue the ROLLBACK command (as you would for an incorrect insert) to void the update. See Day 11 for more on the ROLLBACK command.
DELETE FROM tablename WHERE conditionThe first thing you will probably notice about the DELETE command is that it doesn't have a prompt. Users are accustomed to being prompted for assurance when, for instance, a directory or file is deleted at the operating system level. Are you sure? (Y/N) is a common question asked before the operation is performed. Using SQL, when you instruct the DBMS to delete a group of records from a table, it obeys your command without asking. That is, when you tell SQL to delete a group of records, it will really do it!
On Day 11 you will learn about transaction control. Transactions are database operations that enable programmers to either COMMIT or ROLLBACK changes to the database. These operations are very useful in online transaction-processing applications in which you want to execute a batch of modifications to the database in one logical execution. Data integrity problems will occur if operations are performed while other users are modifying the data at the same time. For now, assume that no transactions are being undertaken.
Depending on the use of the DELETE statement's WHERE clause, SQL can do the following:
NOTE: Some implementations, for example, Oracle, automatically issue a COMMIT command when you exit SQL.
SQL> DELETE FROM COLLECTION 2 WHERE WORTH < 275; 4 rows deleted.The result is a table that looks like this:
SQL> SELECT * FROM COLLECTION; ITEM WORTH REMARKS -------------------- --------- ------------------------------ NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES STRING 1000 SOME DAY IT WILL BE VALUABLE
Example 8.8 uses all three data manipulation statements to perform a set of database operations.
WARNING: Like the UPDATE statement, if you omit a WHERE clause from the DELETE statement, all rows in that particular table will be deleted.
SQL> INSERT INTO COLLECTION 2 VALUES('CHIA PET', 5,'WEDDING GIFT');
1 row created.
SQL> INSERT INTO COLLECTION 2 VALUES('TRS MODEL III', 50, 'FIRST COMPUTER');
1 row created.Now create a new table and copy this data to it:
SQL> CREATE TABLE TEMP 2 (NAME CHAR(20), 3 VALUE NUMBER, 4 REMARKS CHAR(40)); Table created.
SQL> INSERT INTO TEMP(NAME, VALUE, REMARKS) 2 SELECT ITEM, WORTH, REMARKS 3 FROM COLLECTION; 4 rows created.
SQL> SELECT * FROM TEMP; NAME VALUE REMARKS -------------------- --------- ------------------------------ NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES STRING 1000 SOME DAY IT WILL BE VALUABLE CHIA PET 5 WEDDING GIFT TRS MODEL III 50 FIRST COMPUTERNow change some values:
SQL> UPDATE TEMP 2 SET VALUE = 100 3 WHERE NAME = 'TRS MODEL III'; 1 row updated.
SQL> UPDATE TEMP 2 SET VALUE = 8 3 WHERE NAME = 'CHIA PET'; 1 row updated.
SQL> SELECT * FROM TEMP; NAME VALUE REMARKS -------------------- --------- ---------------------------- NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES STRING 1000 SOME DAY IT WILL BE VALUABLE CHIA PET 8 WEDDING GIFT TRS MODEL III 100 FIRST COMPUTERAnd update these values back to the original table:
INSERT COLLECTION SELECT * FROM TEMP; DROP TABLE TEMP;
To check what you have done, select out the records from the COLLECTION table. You will see that the changes you made now exist in the COLLECTION table.
SQL> SELECT * FROM COLLECTION;
NAME VALUE REMARKS -------------------- -------- ---------------------------- NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES STRING 1000 SOME DAY IT WILL BE VALUABLE CHIA PET 8 WEDDING GIFT TRS MODEL III 100 FIRST COMPUTER
WARNING: Always keep in mind that any modifications can affect the referential integrity of your database. Think through all your database editing steps to make sure that you have updated all tables correctly.
Nearly all database systems allow you to import and export data using ASCII text file formats. Although the SQL language does not include this feature, SQL will not do you (or your boss) much good when you have an empty database. We will examine the import/export tools available in the following products: Microsoft Access, Microsoft and Sybase SQL Server, and Personal Oracle7.
Access allows you to import and export various database formats, including dBASE, FoxPro, and SQL Database. The SQL Database option is actually an ODBC data source connection. (Microsoft ODBC is covered on Day 13, "Advanced SQL Topics.") For this discussion, you want to select the Export option and then choose the Text (Fixed Width) option.
After opening an Access database (with the File | Open), select Export. A Destination dialog box (for Exporting) is displayed. Select the Text (Fixed Width) option. This option allows you to output your Access tables to text files in which each data type is a fixed width. For example, a character data field of length 30 will be output to the file as a field 30 characters long. If the field's data takes up less space than 30 characters, it will be padded with spaces. Eventually, you will be asked to set up the export file format. Figure 8.1 shows the Import/Export Setup dialog box.
Figure 8.1.
The Import/Export Setup dialog box.
Notice that in this dialog box you can select the Text Delimiter and the Field Separator for your export file. As a final step, save the specification for use later. This specification is stored internally within the database.
SQL Server provides file import/export capabilities with the bcp tool. bcp is short for "bulk copy." The basic concept behind bcp is the same as that behind Microsoft Access. Unfortunately, the bcp tool requires you to issue commands from the operating system command prompt, instead of through dialog boxes or windows.
Bcp imports and exports fixed-width text files. It is possible to export a file using the Microsoft Access method described earlier and then import that same file directly into an SQL Server table using bcp. bcp uses format files (usually with an .FMT extension) to store the import specification. This specification tells bcp the column names, field widths, and field delimiters. You can run bcp from within an SQL database build script to completely import data after the database has been built.
The SQL*Loader dialog box appears in Figure 8.2.
Figure 8.2.
The SQL*Loader dialog box.
The INSERT statement has two variations. The INSERT...VALUES statement inserts a set of values into one record. The INSERT...SELECT statement is used in combination with a SELECT statement to insert multiple records into a table based on the contents of one or more tables. The SELECT statement can join multiple tables, and the results of this join can be added to another table.
The UPDATE statement changes the values of one or more columns based on some condition. This updated value can also be the result of an expression or calculation.
The DELETE statement is the simplest of the three statements. It deletes all rows from a table based on the result of an optional WHERE clause. If the WHERE clause is omitted, all records from the table are deleted.
Modern database systems supply various tools for data manipulation. Some of these tools enable developers to import or export data from foreign sources. This feature is particularly useful when a database is upsized or downsized to a different system. Microsoft Access, Microsoft and Sybase SQL Server, and Personal Oracle7 include many options that support the migration of data between systems.
A No. Import and export are implementation-specific operations. In other words, the ANSI committee allows individual manufacturers to create whatever features or enhancements they feel are necessary.
Q Can I copy data from a table into itself using the INSERT command? I would like to make duplicate copies of all the existing records and change the value of one field.
A No, you cannot insert data into the same table that you selected from. However, you can select the original data into a temporary table. (True temporary tables are discussed on Day 14.) Then modify the data in this temporary table and select back into the original table. Make sure that you watch out for unique fields you may have already created. A unique field means that the particular field must contain a unique value for each row of data that exists in its table.
Q You have stressed using caution when issuing INSERT, UPDATE, and DELETE commands, but simple fixes seem to be available to correct whatever I did wrong. Is that a fair statement?
A Yes. For example, a simple way to fix a misspelled name is to issue a ROLLBACK command and redo the insert. Another fix would be to do an update to fix the name. Or you could delete the row and redo the insert with the corrected spelling of the name.
But suppose you inserted a million rows into a table and didn't notice that you had misspelled a name when you issued the COMMIT command. A few weeks later, someone notices some bad data. You have had two weeks' worth of database activity. You would more than likely have to issue individual updates to make individual corrections, instead of making any type of global change. In most cases you probably will not know what to change. You may have to restore the database.
DELETE COLLECTION;
INSERT INTO COLLECTION SELECT * FROM TABLE_2
UPDATE COLLECTION ("HONUS WAGNER CARD", 25000, "FOUND IT");
SQL> DELETE * FROM COLLECTION;
SQL> DELETE FROM COLLECTION;
SQL> UPDATE COLLECTION SET WORTH = 555 SET REMARKS = 'UP FROM 525';
SQL> INSERT INTO COLLECTION SET VALUES = 900 WHERE ITEM = 'STRING';
SQL> UPDATE COLLECTION SET VALUES = 900 WHERE ITEM = 'STRING';
2. Using your database system, try exporting a table (or an entire database) to some other format. Then import the data back into your database. Familiarize yourself with this capability. Also, export the tables to another database format if your DBMS supports this feature. Then use the other system to open these files and examine them.