Web based School

- Chapter 8 -
Manipulating Data

Objectives

ToChapter we discuss data manipulation. By the end of the day, you should understand:

How to manipulate data using the INSERT, UPDATE, and DELETE commands
The importance of using the WHERE clause when you are manipulating data
The basics of importing and exporting data from foreign data sources

Introduction to Data Manipulation Statements

Up to this point you have learned how to retrieve data from a database using every selection criterion imaginable. After this data is retrieved, you can use it in an application program or edit it. Week 1 focused on retrieving data. However, you may have wondered how to enter data into the database in the first place. You may also be wondering what to do with data that has been edited. ToChapter we discuss three SQL statements that enable you to manipulate the data within a database's table. The three statements are as follows:

The INSERT statement
The UPDATE statement
The DELETE statement

You may have used a PC-based product such as Access, dBASE IV, or FoxPro to enter your data in the past. These products come packaged with excellent tools to enter, edit, and delete records from databases. One reason that SQL provides data manipulation statements is that it is primarily used within application programs that enable the user to edit the data using the application's own tools. The SQL programmer needs to be able to return the data to the database using SQL. In addition, most large-scale database systems are not designed with the database designer or programmer in mind. Because these systems are designed to be used in high-volume, multiuser environments, the primary design emphasis is placed on the query optimizer and data retrieval engines.

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.

The INSERT Statement

The INSERT statement enables you to enter data into the database. It can be broken down into two statements:

INSERT...VALUES

and

INSERT...SELECT

The INSERT...VALUES Statement

The INSERT...VALUES statement enters data into a table one record at a time. It is useful for small operations that deal with just a few records. The syntax of this statement is as follows:

SYNTAX:

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:

The values used must be the same data type as the fields they are being added to.
The data's size must be within the column's size. For instance, you cannot add an 80-character string to a 40-character column.
The data's location in the VALUES list must correspond to the location in the column list of the column it is being added to. (That is, the first value must be entered into the first column, the second value into the second column, and so on.)

Example 8.1

Assume you have a COLLECTION table that lists all the important stuff you have collected. You can display the table's contents by writing

INPUT:

SQL> SELECT * FROM COLLECTION;

which would yield this:

OUTPUT:

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

If you wanted to add a new record to this table, you would write

INPUTOUTPUT:

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:

INPUT/OUTPUT:

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

ANALYSIS:

The INSERT statement does not require column names. If the column names are not entered, SQL lines up the values with their corresponding column numbers. In other words, SQL inserts the first value into the first column, the second value into the second column, and so on.

Example 8.2

The following statement inserts the values from Example 8.1 into the table:

INPUT:

SQL> INSERT INTO COLLECTION VALUES
  2  ('STRING',1000.00,'SOME Chapter IT WILL BE VALUABLE');

     1 row created.

ANALYSIS:

By issuing the same SELECT statement as you did in Example 8.1, you can verify that the insertion worked as expected:

INPUT:

SQL> SELECT * FROM COLLECTION;

OUTPUT:

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 Chapter IT WILL BE VALUABLE

6 rows selected.

Inserting NULL Values

On Chapter 9, "Creating and Maintaining Tables," you learn how to create tables using the SQL CREATE TABLE statement. For now, all you need to know is that when a column is created, it can have several different limitations placed upon it. One of these limitations is that the column should (or should not) be allowed to contain NULL values. A NULL value means that the value is empty. It is neither a zero, in the case of an integer, nor a space, in the case of a string. Instead, no data at all exists for that record's column. If a column is defined as NOT NULL (that column is not allowed to contain a NULL value), you must insert a value for that column when using the INSERT statement. The INSERT is canceled if this rule is broken, and you should receive a descriptive error message concerning your error.

WARNING: You could insert spaces for a null column, but these spaces will be treated as a value. NULL simply means nothing is there.

INPUT:

SQL> insert into collection values
  2  ('SPORES MILDEW FUNGUS', 50.00, ' ');

OUTPUT:

 1 row inserted.

ANALYSIS:

Using '' instead of NULL inserted a space in the collection table. You then can select the space.

INPUT/OUTPUT:

SQL> select * from collection
  2  where remarks = ' ';

ITEM                         WORTH    REMARKS
--------------------------- -------- ---------
SPORES MILDEW FUNGUS         50.00

1 row selected.

ANALYSIS:

The resulting answer comes back as if a NULL is there. With the output of character fields, it is impossible to tell the difference between a null value and a mere space.

Assume the column REMARKS in the preceding table has been defined as NOT NULL. Typing

INPUT/OUTPUT:

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.

Inserting Unique Values

Many database management systems also allow you to create a UNIQUE column attribute. This attribute means that within the current table, the values within this column must be completely unique and cannot appear more than once. This limitation can cause problems when inserting or updating values into an existing table, as the following exchange demonstrates:

INPUT:

SQL> INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING');

OUTPUT:

INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')
            *
ERROR at line 1:
ORA-00001: unique constraint (PERKINS.UNQ_COLLECTION_ITEM) violated

ANALYSIS:

In this example you tried to insert another ITEM called STRING into the COLLECTION table. Because this table was created with ITEM as a unique value, it returned the appropriate error. ANSI SQL does not offer a solution to this problem, but several commercial implementations include extensions that would allow you to use something like the following:

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 Chapter 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 Chapter 11, "Controlling Transactions," for more on the ROLLBACK command.

The INSERT...SELECT Statement

The INSERT...VALUES statement is useful when adding single records to a database table, but it obviously has limitations. Would you like to use it to add 25,000 records to a table? In situations like this, the INSERT...SELECT statement is much more beneficial. It enables the programmer to copy information from a table or group of tables into another table. You will want to use this statement in several situations. Lookup tables are often created for performance gains. Lookup tables can contain data that is spread out across multiple tables in multiple databases. Because multiple-table joins are slower to process than simple queries, it is much quicker to execute a SELECT query against a lookup table than to execute a long, complicated joined query. Lookup tables are often stored on the client machines in client/server environments to reduce network traffic.

Many database systems also support temporary tables. (See Chapter 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:

INPUT:

SQL> insert into tmp_tbl
  2  select * from table;

OUTPUT:

19,999 rows inserted.

ANALYSIS:

You are selecting all the rows that are in table and inserting them into tmp_tbl.

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 Chapter 14 for a more detailed treatment of this topic.

The syntax of the INSERT...SELECT statement is as follows:

SYNTAX:

INSERT INTO table_name
(col1, col2...)
SELECT col1, col2...
FROM tablename
WHERE search_condition

Essentially, 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.

Example 8.3

This example creates the new table INVENTORY.

INPUT:

SQL> CREATE TABLE INVENTORY
  2  (ITEM CHAR(20),
  3  COST NUMBER,
  4  ROOM CHAR(20),
  5  REMARKS CHAR(40));

OUTPUT:

Table created.

The following INSERT fills the new INVENTORY table with data from COLLECTION.

INPUT/OUTPUT:

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:

INPUT/OUTPUT:

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 Chapter 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 Chapter 11 for more on the COMMIT command.

ANALYSIS:

You have successfully, and somewhat painlessly, moved the data from the COLLECTION table to the new INVENTORY table!

The INSERT...SELECT statement requires you to follow several new rules:

The SELECT statement cannot select rows from the table that is being inserted into.
The number of columns in the INSERT INTO statement must equal the number of columns returned from the SELECT statement.
The data types of the columns in the INSERT INTO statement must be the same as the data types of the columns returned from the SELECT statement.

Another use of the INSERT...SELECT statement is to back up a table that you are going to drop, truncate for repopulation, or rebuild. The process requires you to create a temporary table and insert data that is contained in your original table into the temporary table by selecting everything from the original table. For example:

   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 toChapter 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.

The UPDATE Statement

The purpose of the UPDATE statement is to change the values of existing records. The syntax is

SYNTAX:

UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
WHERE search_condition

This 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.

Example 8.4

This example illustrates the use of the UPDATE statement:

INPUT:

SQL> UPDATE COLLECTION
  2  SET WORTH = 900
  3  WHERE ITEM = 'STRING';

OUTPUT:

1 row updated.

To confirm the change, the query

INPUT/OUTPUT:

SQL> SELECT * FROM COLLECTION
  2  WHERE ITEM = 'STRING';

yields

ITEM                     WORTH REMARKS
-------------------- --------- ------------------------------

STRING                     900 SOME Chapter IT WILL BE VALUABLE

Here is a multiple-column update:

INPUT/OUTPUT:

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.

Example 8.5

If the WHERE clause is omitted, every record in the COLLECTION table is updated with the value given.

INPUT/OUTPUT:

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:

INPUT/OUTPUT:

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 Chapter IT WILL BE VALUABLE

6 rows selected.

You, of course, should check whether the column you are updating allows unique values only.

WARNING: If you omit the WHERE clause from the UPDATE statement, all records in the given table are updated.

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:

SYNTAX:

UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
FROM table_list
WHERE search_condition

Example 8.6

Here's an example of the extension:

INPUT:

SQL> UPDATE COLLECTION
  2  SET WORTH = WORTH * 0.005;

that changes the table to this:

INPUT/OUTPUT:

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 Chapter IT WILL BE VALUABLE

6 rows selected.

ANALYSIS:

This syntax is useful when the contents of one table need to be updated following the manipulation of the contents of several other tables. Keep in mind that this syntax is nonstandard and that you need to consult the documentation for your particular database management system before you use it.

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 Chapter 11 for more on the ROLLBACK command.

The DELETE Statement

In addition to adding data to a database, you will also need to delete data from a database. The syntax for the DELETE statement is

SYNTAX:

DELETE FROM tablename
WHERE condition

The 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 Chapter 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.

NOTE: Some implementations, for example, Oracle, automatically issue a COMMIT command when you exit SQL.

Depending on the use of the DELETE statement's WHERE clause, SQL can do the following:

Delete single rows
Delete multiple rows
Delete all rows
Delete no rows

Here are several points to remember when using the DELETE statement:

The DELETE statement cannot delete an individual field's values (use UPDATE instead). The DELETE statement deletes entire records from a single table.
Like INSERT and UPDATE, deleting records from one table can cause referential integrity problems within other tables. Keep this potential problem area in mind when modifying data within a database.
Using the DELETE statement deletes only records, not the table itself. Use the DROP TABLE statement (see Chapter 9) to remove an entire table.

Example 8.7

This example shows you how to delete all the records from COLLECTION where WORTH is less than 275.

INPUT:

SQL> DELETE FROM COLLECTION
  2  WHERE WORTH < 275;

4 rows deleted.

The result is a table that looks like this:

INPUT/OUTPUT:

SQL> SELECT * FROM COLLECTION;

ITEM                     WORTH REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES

STRING                    1000 SOME Chapter IT WILL BE VALUABLE

WARNING: Like the UPDATE statement, if you omit a WHERE clause from the DELETE statement, all rows in that particular table will be deleted.

Example 8.8 uses all three data manipulation statements to perform a set of database operations.

Example 8.8

This example inserts some new rows into the COLLECTION table you used earlier today.

INPUT:

SQL> INSERT INTO COLLECTION
  2  VALUES('CHIA PET', 5,'WEDDING GIFT');

OUTPUT:

1 row created.

INPUT:

SQL> INSERT INTO COLLECTION
  2  VALUES('TRS MODEL III', 50, 'FIRST COMPUTER');

OUTPUT:

1 row created.

Now create a new table and copy this data to it:

INPUT/OUTPUT:

SQL> CREATE TABLE TEMP
  2  (NAME CHAR(20),
  3  VALUE NUMBER,
  4  REMARKS CHAR(40));

Table created.

INPUT/OUTPUT:

SQL> INSERT INTO TEMP(NAME, VALUE, REMARKS)
  2  SELECT ITEM, WORTH, REMARKS
  3  FROM COLLECTION;

4 rows created.

INPUT/OUTPUT:

SQL> SELECT * FROM TEMP;

NAME                     VALUE REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME Chapter IT WILL BE VALUABLE
CHIA PET                     5 WEDDING GIFT
TRS MODEL III               50 FIRST COMPUTER

Now change some values:

INPUT/OUTPUT:

SQL> UPDATE TEMP
  2  SET VALUE = 100
  3  WHERE NAME = 'TRS MODEL III';

1 row updated.

INPUT/OUTPUT:

SQL> UPDATE TEMP
  2  SET VALUE = 8
  3  WHERE NAME = 'CHIA PET';

1 row updated.

INPUT/OUTPUT:

SQL> SELECT * FROM TEMP;

NAME                     VALUE REMARKS
-------------------- --------- ----------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME Chapter IT WILL BE VALUABLE
CHIA PET                     8 WEDDING GIFT
TRS MODEL III              100 FIRST COMPUTER

And update these values back to the original table:

INPUT:

INSERT COLLECTION
SELECT * FROM TEMP;
DROP TABLE TEMP;

ANALYSIS:

The DROP TABLE and CREATE TABLE statements are discussed in greater detail on Chapter 9. For now, these statements basically do what their names suggest. CREATE TABLE builds a new table with the format you give it, and DROP TABLE deletes the table. Keep in mind that DROP TABLE permanently removes a table, whereas DELETE FROM <TableName> removes only the records from a table.

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.

INPUT/OUTPUT:

SQL> SELECT * FROM COLLECTION;

NAME                     VALUE REMARKS
-------------------- -------- ----------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME Chapter IT WILL BE VALUABLE
CHIA PET                     8 WEDDING GIFT
TRS MODEL III              100 FIRST COMPUTER

ANALYSIS:

The previous example used all three data manipulation commands--INSERT, UPDATE, and DELETE--to perform a set of operations on a table. The DELETE statement is the easiest of the three to use.

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.

Importing and Exporting Data from Foreign Sources

The INSERT, UPDATE, and DELETE statements are extremely useful from within a database program. They are used with the SELECT statement to provide the foundation for all other database operations you will perform. However, SQL as a language does not have a way to import or export of data from foreign data sources. For instance, your office may have been using a dBASE application for several years now that has outgrown itself. Now your manager wants to convert this application to a client/server application using the Oracle RDBMS. Unfortunately for you, these dBASE files contain thousands of records that must be converted to an Oracle database. Obviously, the INSERT, UPDATE, and DELETE commands will help you after your Oracle database has been populated, but you would rather quit than retype 300,000 records. Fortunately, Oracle and other manufacturers provide tools that will assist you in this task.

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.

Microsoft Access

Microsoft Access is a PC-only database product that contains many of the features of a relational database management system. Access also includes powerful reporting tools, a macro language similar to Visual Basic, and the capability to import and export data from various database and text file formats. This section examines this last feature, particularly the capability to export to delimited text files. Delimited means that each field is separated, or delimited, by some special character. This character is often a comma, a quotation mark, or a space.

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 Chapter 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.

Microsoft and Sybase SQL Server

Microsoft and Sybase have jointly developed a powerful database system that is very popular in client/server application development. The name of this system is SQL Server. Microsoft has agreed to develop versions of the RDBMS for some platforms, and Sybase has developed its version for all the other platforms (usually the larger ones). Although the arrangement has changed somewhat in recent years, we mention this agreement here to help you avoid confusion when you begin examining the various database systems available on the market today.

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.

Personal Oracle7

Personal Oracle7 allows you to import and export data from ASCII text files containing delimited or fixed-length records. The tool you use is SQL*Loader. This graphical tool uses a control file (with the .CTL extension). This file is similar to SQL Server's format (FMT) file. The information contained in this file tells SQL*Loader what it needs to know to load the data from the file.

The SQL*Loader dialog box appears in Figure 8.2.

Figure 8.2.
The SQL*Loader dialog box.

Summary

SQL provides three statements that you can use to manipulate data within a database.

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.

Q&A

Q Does SQL have a statement for file import/export operations?

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 Chapter 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.

Workshop

The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."

Quiz

1. What is wrong with the following statement?

DELETE COLLECTION;

2. What is wrong with the following statement?

INSERT INTO COLLECTION 
SELECT * FROM TABLE_2

3. What is wrong with the following statement?

UPDATE COLLECTION ("HONUS WAGNER CARD", 
25000, "FOUND IT");

4. What would happen if you issued the following statement?

SQL> DELETE * FROM COLLECTION;

5. What would happen if you issued the following statement?

SQL> DELETE FROM COLLECTION;

6. What would happen if you issued the following statement?

SQL> UPDATE COLLECTION
     SET WORTH = 555
     SET REMARKS = 'UP FROM 525';

7. Will the following SQL statement work?

SQL> INSERT INTO COLLECTION
     SET VALUES = 900
     WHERE ITEM = 'STRING';

8. Will the following SQL statement work?

SQL> UPDATE COLLECTION
     SET VALUES = 900
     WHERE ITEM = 'STRING';

Exercises

1. Try inserting values with incorrect data types into a table. Note the errors and then insert values with correct data types into the same table.

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.