Teradata ALTER TABLE ADD COLUMN

Teradata ALTER TABLE ADD COLUMN statement is used to add a new column in an existing Teradata table. Multiple columns also can be added in a single command using the ALTER TABLE ADD COLUMN statement. Apart from the adding a new column, the ADD function has the capabilities of adding data types, size, allowing of NULL values, default value associated with it.

Teradata ALTER TABLE ADD COLUMN syntax

Following is the syntax of the ALTER TABLE ADD COLUMN statement.

ALTER TABLE database_name.tbl_name 
ADD 
column_name column_attributes;

Here,

  • database_name – The name of the owner database or user where the table resides.
  • tbl_name – The name of the table in which you want to add the new column.
  • column_name – The name of the column which you want to add.
  • column_attributes – Define attributes like datatype, length, NULL values, and default values.

Teradata ALTER TABLE ADD COLUMN example

Let’s understand the concept of the Teradata ALTER TABLE ADD COLUMN with the help of some examples.

First, create an Employee table in TUTORIALSBOOK database as below.

CREATE SET TABLE TUTORIALSBOOK.Employee, NO FALLBACK
   (EMPNO   INTEGER NOT NULL,
    FIRST_NAME   CHAR(25) NOT NULL,
    LAST_NAME   CHAR(25),
    HIREDATE    DATE FORMAT 'YYYY-MM-DD' NOT NULL
   )
UNIQUE PRIMARY INDEX (EMPNO);

Second, insert a few records into the table.

INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1001,'STEFAN','SALVATORE','2001-04-12');
INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1002,'DIANA','LORANCE','2005-01-05');
INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1003,'JAMES','MADISON','2005-09-18');
INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1004,'JONES','NICK','2004-08-19');
INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1005,'LUCY','GILLER','2006-04-01');
INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1006,'ISSAC','STEFAN','2002-11-18');
INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1007,'NANCY','GILBERT','2003-04-06');
INSERT INTO TUTORIALSBOOK.EMPLOYEE VALUES (1008,'JOHN','SMITH','2005-10-12');

Third, check the content of the Employee table using the select statement.

SELECT * FROM TUTORIALSBOOK.Employee;

 *** Query completed. 8 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

      EMPNO FIRST_NAME                LAST_NAME                   HIREDATE
----------- ------------------------- ------------------------- ----------
       1008 JOHN                      SMITH                     2005-10-12
       1004 JONES                     NICK                      2004-08-19
       1006 ISSAC                     STEFAN                    2002-11-18
       1002 DIANA                     LORANCE                   2005-01-05
       1007 NANCY                     GILBERT                   2003-04-06
       1003 JAMES                     MADISON                   2005-09-18
       1005 LUCY                      GILLER                    2006-04-01
       1001 STEFAN                    SALVATORE                 2001-04-12

Now, we want to add another column named SALARY to the Employee table. The following statement will do the same.

ALTER TABLE TUTORIALSBOOK.Employee
ADD
SALARY DECIMAL(8,2) NOT NULL DEFAULT 0.00;

 *** Table has been modified.
 *** Total elapsed time was 1 second.

You can verify the structure of the Employee table using the SHOW TABLE command.

SHOW TABLE TUTORIALSBOOK.Employee;

 *** Text of DDL statement returned.
 *** Total elapsed time was 1 second.

---------------------------------------------------------------------------
CREATE SET TABLE TUTORIALSBOOK.Employee ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      EMPNO INTEGER NOT NULL,
      FIRST_NAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      LAST_NAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      HIREDATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      SALARY DECIMAL(8,2) NOT NULL DEFAULT 0.00 )
UNIQUE PRIMARY INDEX ( EMPNO );

As you can see that new column SALARY has been now added to the table.

Example 2: Add Multiple Columns to the Teradata Table

The below example shows you how to add multiple columns to an existing Teradata table.

ALTER TABLE TUTORIALSBOOK.Employee
ADD
DESIGNATION VARCHAR(50),
ADD DEPTNO INT NOT NULL DEFAULT 0;

 *** Table has been modified.
 *** Total elapsed time was 1 second.

The above statement added two new columns DESIGNATION and DEPTNO to the Employee table.

Finally check the table data using the select statement.

Teradata Alter TABLE ADD COLUMN

Please get connected & share!

Advertisement