Example: Inserting Data from a Source Table Into a New Table - Advanced SQL Engine - Teradata Database

This example takes data from a source table which records a phone number associated with an employee and inserts it into a new table which records phone numbers in an ARRAY type. In many cases, an employee may have multiple phone numbers, such as office phone, mobile phone, home phone, etc. A better way to represent this type of data is with a 1-D ARRAY type which records multiple phone numbers. This reduces the number of rows in the table.

Note that the same type of behavior can be illustrated with an n-D ARRAY type. The only difference is that the storage of elements is done in row-major order.

The following statement creates a 1-D ARRAY type that can hold up to 100 phone number values:

CREATE TYPE emp_phone_array AS VARCHAR(14) ARRAY[100];

The following source table contains one row per employee phone number.

CREATE SET TABLE employee
   (emp_id INTEGER,
    emp_name VARCHAR(30),
    emp_phone CHAR(14)); 
SELECT * FROM employee;
  emp_id      emp_name           emp_phone
--------   -----------           -------------
       1         Beth           (619) 619-6190
       1         Beth           (619) 620-6200
       1         Beth           (619) 720-7200
       2         Greg           (858) 858-8580
       2         Greg           (858) 859-8590
       2         Greg           (858) 860-8600
       3         Louise         (421) 421-4210
       3         Louise         (421) 422-4220
       3         Louise         (421) 423-4230

The following target table contains one row per employee and stores all phone numbers associated with an employee in an ARRAY type.

CREATE SET TABLE employeePhoneInfo
   (emp_id INTEGER,
    emp_name VARCHAR(30),
    emp_phone emp_phone_array);
INSERT INTO employeePhoneInfo
   SELECT emp_id, emp_name, 
      ARRAY_AGG(emp_phone, NEW emp_phone_array())
   FROM employee GROUP BY emp_id,emp_name 
         WHERE emp_id < 100;
SELECT * FROM employeePhoneInfo;

The result is:

emp_id      emp_name           emp_phone
--------   -----------           -------------
       1         Beth           ( (619) 619-6190, (619) 620-6200, (619) 720-7200 )
       2         Greg           ( (858) 858-8580, (858) 859-8590, (858) 860-8600 )
       3         Louise         ( (421) 421-4210, (421) 422-4220, (421) 423-4230 )