Command Discussion

• The INSERT INTO clause indicates that you intend to add a row or rows to a table.

• Following the INSERT INTO clause, the user specifies the name of the table to be updated.

• The query is evaluated, and a copy of the results from the query is stored in the table specified after the INSERT INTO clause. If rows already exist in the table being copied to, then the new rows are added to the end of the table.

• Block inserts of text columns using INDIRECT respond just as any other column.

Example: Finance wants to do an analysis by department of the consequences of a company wide 10% raise in salaries, as it would affect overall departmental budgets. We want to manipulate the relational information stored in the database without affecting the actual table in use.

Step 1: Create a new table named EMP_RAISE, where the projected results can be studied without affecting the live stored information. Use this CREATE TABLE statement, which defines data types as in the original table, EMPLOYEE, creating an empty table.

CREATE TABLE  EMP_RAISE
       (EID       INTEGER
        ENAME     CHAR(15)
        DEPT      CHAR(3)
        RANK      CHAR(5)
        BENEFITS  CHAR(4)
        SALARY    INTEGER) ;

Step 2: Copy the data in the EMPLOYEE table to the EMP_RAISE table. We will later change salaries to the projected new salaries using the UPDATE command. For now, the new table must be loaded as follows:

INSERT INTO  EMP_RAISE
       SELECT  *
       FROM    EMPLOYEE ;

The number of records which exist in the EMPLOYEE table at the time this INSERT INTO command is done is the number of records which will be created in the new EMP_RAISE table. Now that the new table has data values, it can be queried and updated, without affecting the data in the EMPLOYEE table.

An easier way to create a copy of the table is to use the following syntax:

CREATE TABLE  EMP_RAISE AS
       SELECT  *
       FROM    EMPLOYEE ;

which creates the table, and copies it in one statement. Any indexes on the original table will not be created on the new one.