How to ALTER, TRUNCATE, or DROP Reference Tables

To edit a column name or add or remove any column from a Reference Table, run an SQL query on the table in question.

Create an SQL Access Object to Alter the table

This SQL Access Object is used to alter a table in the CIC Internal Database.

1. Create a SQL Access Object (File > New > SQL Access Object). For details, see SQL Access.
2. Add the SQL QUERY for Table Alteration. See below for examples. 
   
   

Alter Table SQL 

Syntax: ALTERTABLE "REFTABLES"."<TABLE_NAME>" ADD ‘<COLUMN_NAME>’ <DATATYPE>;
Example: ALTER TABLE "REFTABLES"."PURCHASE_ORDERS" ADD “SKU” CHARACTER VARYING(25);

Syntax: ALTERTABLE "REFTABLES"."<TABLE_NAME>" RENAME COLUMN“<NEW_TABLE_NAME>”to“<OLD_TABLE_NAME>”;
Example: ALTER TABLE "REFTABLES"."PURCHASE_ORDERS" RENAME “SKU” TO “NEW_SKU”;

Syntax: ALTERTABLE "REFTABLES"."<TABLE_NAME>"  DROP COLUMN "<COLUMN_NAME>”;
Example: ALTER TABLE "REFTABLES"."PURCHASE_ORDERS" DROP COLUMN “SKU” CHARACTER VARYING(25);

Truncate Table SQL

Use Truncate functionality to Delete all the data from a table.

Syntax: TRUNCATETABLE "REFTABLES"."<TABLE_NAME>";
Example: TRUNCATE TABLE "REFTABLES"."PURCHASE_ORDERS";

Drop Table SQL

Use the Drop function to delete a table.

Syntax: DROPTABLE "REFTABLES"."<TABLE_NAME>";
Example: DROP TABLE "REFTABLES"."PURCHASE_ORDERS";