Microsoft syntax is slightly different:
CREATE #tmp_Intermediate ( field1 INTEGER
, field2 VARCHAR2(20) );
The hash sign indicates the fl eeting nature of the created table. The table automatically disappears once the user disconnects from the database. A double hash (##) gives the temporary table global scope, meaning that this table is visible to different users, and will disappear once all users disconnect — a subtle but important difference.
Once a temporary table is created, you can use it just as any other table for INSERT, UPDATE, and DELETE. Temporary tables can be used as intermediate storage or a workbench for your results, and are mostly used in stored procedures (see Chapter 4).
Temporary tables have a number of restrictions that do not apply to permanent tables. Full discussion of these nuances is beyond the scope of this book. Temporary tables are defi ned in SQL Standard, and all RDBMSs offer an ability to create temporary tables, though there are a lot of implementation differences.
ALTER TABLE
Is there a better way to rewrite the past? Indeed, there is: Enter the ALTER statement. Say you’ve created a table only to discover that you’ve added a superfl uous fi eld, specifi ed a fi eld (column) of the wrong type, or missed the column you need and need to add one. The ALTER statement can take on these jobs, and then some.
c02.indd 49
c02.indd 49 3/15/2011 12:10:44 PM3/15/2011 12:10:44 PM
www.it-ebooks.info
SQL Standard does not allow you to use the ALTER statement to change a data type, but it allows you to drop and re-create a fi eld, which amounts to the same thing.
With ALTER, you can change the object by renaming it or changing its RDBMS–specifi c advanced characteristics (for example, move it to a different TABLESPACE in Oracle):
ALTER TABLE books RENAME to new_books;
Before renaming a table, you have to understand all the ramifi cations of the action because other objects might depend on the table (we’ll discuss these in subsequent chapters). Once you rename it, all these dependencies will be broken. Not all RDBMSs approach the table-renaming task in the same fashion. While ALTER...RENAME will work with Oracle, PostgreSQL, and MySQL, it will not work in Microsoft products or in BD2 or OpenOffi ce BASE/HSQLDB.
Microsoft SQL Server uses system stored procedures (see Chapter 4) to rename objects (including tables and columns). This command will do the trick of renaming the BOOKS table into the NEW_BOOKS table:
EXEC sp_rename ‘books’,’new_books’
IBM DB2 employs a separate RENAME statement:
RENAME books TO SYSTEM NAME new_books
All warnings regarding renaming database objects fully apply here; proceed with extreme caution!
Adding a new column to a table is rather straightforward. The following statement alters the table by adding a column NEW_COLUMN1 of CHAR data type of exactly one character length:
ALTER TABLE books ADD new_column1 CHAR(1);
This basic syntax is virtually identical across all RDBMSs, including the default COLUMN key-word being optional (with the exception of Microsoft Access and HSQLDB/OpenOffi ce BASE, which require the COLUMN keyword to be there).
Adding more than one column at a time is also supported:
ALTER TABLE books ADD new_column1 CHAR(1) , new_column2 INT , new_column3 DATETIME;
If the column you are trying to add already exists, the RDBMS will warn you about it by throwing an error.
Some databases, notably IBM DB2, place additional restrictions on altered tables before you can have access to them. Only specifi c commands can be issued against the altered table; for example, you can drop or rename it, or alter it some more. To bring the table back online, to be accessible, you must execute the REORG TABLE command to notify the RDBMS that the modifi cations are all done.
c02.indd 50
c02.indd 50 3/15/2011 12:10:44 PM3/15/2011 12:10:44 PM
www.it-ebooks.info
Modifying an existing column is more convoluted, especially if data are already there. With the ALTER statement, you can rename the column, change its data type, or add constraints. For instance, the following syntax is used by different RDBMSs to modify columns:
-- Oracle syntax
-- multiple changes are allowed ALTER TABLE books
MODIFY new_column1 CHAR(2) NOT NULL ,MODIFY new_column2 BIGINT
-- IBM DB2 syntax
-- multiiple changes are allowed ALTER TABLE books
ALTER COLUMN new_column1 SET DATA TYPE CHAR(2) ,ALTER COLUMN new_column1 SET NOT NULL
,ALTER COLUMN new_column2 SET DATA TYPE BIGINT -- Microsoft SQl Server 2008 and MS Access -- only one column at the time can be altered -- cannot be renamed using ALTER statement ALTER TABLE books
ALTER COLUMN new_column1 CHAR(2) ALTER TABLE books
ALTER COLUMN new_column2 BIGINT -- PostgreSQL
ALTER TABLE distributors
ALTER COLUMN new_column1 TYPE CHAR(2) ,ALTER COLUMN new_column2 TYPE BIGINT;
ALTER TABLE books RENAME COLUMN new_colum1 TO old_column1;
-- MySQL
-- only one column at the time can be altered
-- changing data type requires column name to appear twice
-- same syntax used to rename columns, CHANGE could be substituted for MODIFY ALTER TABLE books CHANGE new_column2new_column2BIGINT;
ALTER TABLE books CHANGE new_column2old_column2INTEGER;
Some RDBMSs such as Microsoft SQL Server 2008 will not allow you to alter columns of TEXT, NTEXT, and IMAGE data types. Oracle will prevent you from decreasing the size of a charac-ter data type (or lower precision, total number of digits, for numeric types) non-empty column, HSQLDB does not support renaming columns at all, and so on.
Getting rid of the objects requires the same universal DROP working in conjunction with ALTER statements; the syntax is surprisingly consistent across all RDBMSs, with minor differences as shown in the following. Some RDBMSs allow you to drop several columns in a single statement;
some don’t.
-- Oracle syntax ALTER TABLE books
DROP (new_column2, new_column1);
-- Microsoft SQL Server 2008 and MS Access -- IBM DB2, PostgreSQL and MySQL
ALTER TABLE books
DROP COLUMN new_column2;
c02.indd 51
c02.indd 51 3/15/2011 12:10:45 PM3/15/2011 12:10:45 PM
www.it-ebooks.info
An RDBMS worth its salt would prevent you from dropping all the columns. An attempt to drop the last column would result in a stern warning: “A table must have at least one data column,” or something similar.