Developer Reference : Getting Started : Programmer's Guide : Inserting and Deleting Data
 
Inserting and Deleting Data
This chapter includes the following sections:
Overview of Inserting and Deleting Data
Inserting Values
Transaction Processing
Deleting Data
Dropping Indexes
Dropping Columns
Dropping Tables
Dropping an Entire Database
Overview of Inserting and Deleting Data
After creating a data dictionary, tables, and columns, you can add data to the database using SQL Data Manager. SQL statements allow you to do the following:
Specify literal values to insert.
Select data from other tables and insert the resulting values into entire rows or specified columns.
When you insert a literal value, it must conform to the specified column’s data type and length.
You can drop (delete) rows, indexes, columns, or tables from your database. In addition, you can drop an entire database when you no longer need it.
Inserting Values
You can use a VALUES clause in an INSERT statement to specify literal values to insert into a table. The following example inserts a new row into the Course table of the sample database:
INSERT INTO Course
VALUES ('ART 103', 'Principles of Color', 3, 'Art');
In this example, listing the columns Name, Description, Credit_Hours, and Dept_Name is optional because the statement inserts a value for each column in the table, in order. However, a column list is required if the statement inserted data only into selected columns instead of the entire row, or if the statement inserted data into the columns in a different order than is defined in the table.
For complete information on the INSERT statement, see INSERT in SQL Engine Reference.
Transaction Processing
When you attempt to insert data into a table, Zen returns an error if the data is invalid. Any data inserted before the error occurred is rolled back. This enables your database to remain in a consistent state.
You can use transaction processing in a Zen database to group a set of logically related statements together. Within a transaction, you can use savepoints to effectively nest transactions; if a statement in a nesting level fails, then the set of statements in that nesting level is rolled back to the savepoint. See the following topics in SQL Engine Reference for information about transaction processing and savepoints:
START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT
RELEASE SAVEPOINT
Deleting Data
There are two types of DELETE statements: positioned and searched.
You can use a DELETE statement to delete one or more rows from a table or an updatable view. To specify specific rows for Zen to delete, use a WHERE clause in a DELETE statement.
DELETE FROM Class
WHERE ID = 005#
The positioned DELETE statement deletes the current row of a view associated with an open SQL cursor.
DELETE WHERE CURRENT OF mycursor;
For more information, see DELETE and DELETE (positioned) in SQL Engine Reference.
Dropping Indexes
If you find that you no longer need a named index, use a DROP INDEX statement to drop it.
DROP INDEX DeptHours#
For more information, see DROP INDEX in SQL Engine Reference.
Dropping Columns
To drop a column from a table, use an ALTER TABLE statement.
ALTER TABLE Faculty
DROP Rsch_Grant_Amount#
This example drops the Rsch_Grant_Amount column from the Faculty table and deletes the column definition from the data dictionary.
For more information, see ALTER TABLE in SQL Engine Reference.
Note Be aware that if you use the ALTER TABLE statement on files that contain a lot of data, execution could take some time to complete, and other users may not be able to access data in that file in the meantime.
Dropping Tables
To drop a table from the database, use a DROP TABLE statement.
DROP TABLE Student#
This example drops the InactiveStudents table definition from the data dictionary and deletes its corresponding data file (INACT.MKD).
For more information, see DROP TABLE in SQL Engine Reference.
Note You cannot drop system tables. See SQL Engine Reference for a complete listing of system tables.
Dropping an Entire Database
When you no longer need a particular database, you can delete it using the SQL Data Manager in the Zen Control Center. See the Zen User’s Guide for more information.