I had this example floating around in a notepad for a while, but I find myself coming back it occasionally. So, I thought I would add it to this blog for future reference.
The Table Alter Process
This is an outline of the Alter table process I follow, for reference, in case it is helpful.
- Generate DDL in Aginity and make backup original table structure
- Perform Insert into backup table from original table
- Create Alter SQL
- Execute Alter SQL
- Refresh Aginity table columns
- Generate new DDL
- visually validate DDL Structure
- If correct, archive copy of DDL to version control system
- Preform any update commands, if required, required to populate the new columns.
- Execute post alter table cleanup
- Groom Versions
- Groom table
- Generate statistics
- Once the any required processes and the data have been validated, drop the backup table.
Basic Alter SQL Command Structure
Here is the basic syntax for adding multiple columns:
ALTER TABLE <<OWNER>>.<<TABLENAME>>
ADD COLUMN <<FieldName1>> <<Field Type>> <<Constraint, if applicable>>
, <<FieldName2>> <<Field Type>> <<Constraint, if applicable>>;
Example Alter SQL Command to a Multiple Columns
Here is a quick example, which is adding four columns:
Example SQL Adding Multiple Columns
ALTER TABLE BLOG.PRODUCT_DIM
ADD COLUMN MANUFACTURING_PLANT_KEY NUMERIC(6,0) NOT NULL DEFAULT 0
, LEAD_TIME_PRODUCTION NUMERIC(2,0) NOT NULL DEFAULT 0
, PRODUCT_CYCLE CHARACTER VARYING(15) NOT NULL DEFAULT ‘ ‘::”NVARCHAR”
, PRODUCT_CLASS CHARACTER VARYING(2) NOT NULL DEFAULT ‘ ‘::”NVARCHAR” ;
Cleanup Table SQL Statements
GROOM TABLE BLOG.PRODUCT_DIM VERSIONS;
GROOM TABLE BLOG.PRODUCT_DIM;
GENERATE STATISTICS ON BLOG.PRODUCT_DIM;
Related References
- Data Modeling – Fact Table Effective Practices
- Data Modeling – Dimension Table Effective Practices
- Data Warehouse – Effective Practices
- Database Table Field Ordering Effective Practices
- Database – What is a Composite Primary Key
- Database – What is a Primary Key?
- Netezza/ PureData – how to add a primary key
- PureData / Netezza – Useful links
- Netezza / PureData – How to add a Foreign Key
- Database – What is a foreign key?
