Using INSERT ... SELECT - Advanced SQL Engine - Teradata Database

Use the INSERT ... SELECT statement to load the current data rows quickly into a new table.

The procedure for recreating a table is as follows:

1. Select the explicit privileges of the old table with the following query:

   SELECT username, accessright, grantauthority, columnname, allnessflag FROM dbc.allrightsV  
   WHERE tablename = 'Employee' AND databasename = 'Personnel';

   Save the output for later use; you will need to recreate the explicit privileges on the new table.
2. Create a new table with a temporary name, such as Temp.

   CREATE TABLE Temp_Employee
   (col1 datatype, col2 datatype...)

   To display the DDL for the current table, submit a SHOW TABLE or SHOW IN XML TABLE request.

3. If any data types, columns, column attributes, or the column order are not compatible, use an INSERT ... SELECT statement that constructs compatible values. If this is not possible, you may need to load data from an external source.

   If the data types are compatible, you can transfer all data rows from the old table to the new table with a single INSERT ... SELECT statement:

   INSERT INTO Temp_Employee
   SELECT *
   FROM Employee ;

   If the tables are not compatible, a more complicated SELECT that computes the values for the target table from columns of the source table is needed.

4. Use SHOW JOIN/HASH INDEX or SHOW IN XML JOIN/HASH INDEX to see the index definitions. If needed, drop the hash index or join index using DROP JOIN INDEX or DROP HASH INDEX and recreate it using CREATE JOIN INDEX or CREATE HASH INDEX.
5. Drop the old Employee table:

   DROP TABLE Employee ;

   When the table is dropped, explicit privileges are also dropped because the Data Dictionary references objects by ID rather than by name.
6. Rename the temporary table:

   RENAME TABLE Temp_Employee TO Employee ;

7. Use the index definitions from step 4 to recreate join and hash indexes that you want to maintain for the new table.
8. Submit GRANT statements to re-establish the explicit privileges you saved in step 1 on the new version of Employee table.

Use the LOGGING ERRORS option to log errors. Errors include duplicate row errors, duplicate primary key errors, CHECK constraint errors, and more. For more information, see INSERT and INSERT ... SELECT in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.