Drop Field and Drop Table

[Jennell_McIntire]

The Drop script keyword is often used in scripts to drop a field or a table from the data model and from memory.  In this blog, I will discuss some of ways I use Drop Field and Drop Table in my script.  Drop Field can be used in the script to drop one or more fields from the data model.  The syntax is as follows:

To drop field A from all tables in the data model:

Drop Field A;

To drop field A from only table X:

Drop Field A From X;

To drop more than one field (A and B) from the data model:

Drop Fields A, B;

To drop more than one field (A and B) from multiple tables (X, Y):

Drop Fields A, B From X, Y;

I often use Drop Fields when I am creating a link table in my data model.  You can read more about link tables in Arturo’s Concatenate vs Link Table blog.  When I have more than one table linked by more than one field in my data model, I create a link table to eliminate the synthetic table and link the tables by just one field.  To do this, I first create a composite key in each of my tables.  Then I create a link table that stores the composite key and the fields that make up the composite key.  To avoid creating another synthetic table, I drop the fields that make up my composite key from the original tables.  Using some of Arturo’s example in his technical brief, here is an example of the script:

In the script above, I used Drop Fields to drop the Year, EmployeeID and ProductID from the Sales and Budget tables.  Since these fields are now in the link table, I no longer needed them in the Sales and Budget tables.

Drop Table works in a similar way.  Drop table can be used in the script to drop one or more tables from the data model.  The syntax is as follows:

To drop table X:

Drop Table X;

To drop more than one table (X and Y):

Drop Tables X, Y;

Often in my script I create a new table based on an existing table.  When I do this, I frequently use Drop Table to delete the original table to prevent synthetic tables.  In the example script below, the Drop Table statement deletes the SalesTemp table and all its fields from the data model.  Therefore, the SalesTemp table can no longer be used in the script after the Drop Table statement.

Drop Field and Drop Table are useful scripting statements that help you create a data model that is free of synthetic tables and unnecessary fields and tables.  So the next time you find a synthetic table in your data model, check to make sure you did not forget to drop a field or a table.

Thanks,

Jennell