Dynamic Table Name

Database

Dynamic Table Name

Hi,

I wish to create table in teradata database having dynamic name e.g., having month suffixed at the end
For example, code will create table as abc.sales_July2011
where abc is the database, July is current month - which will change to August in next month and so on
also 2011 will change in next year.

let me know if I need to provide more details.

Please suggest. Thanks

Regards,
DILIPandey, Excel MVP
4 REPLIES
Enthusiast

Re: Dynamic Table Name

Are you creating multiple tables or renaming the existing table?
Are all of these tables supposed to be created initially or are they created as needed?

Re: Dynamic Table Name

Thanks Mnylin for taking interest in this.

Yes, I need to create multiple tables in Teradata.
I am having few sql codes which I need to run in every month and each sql code will create one table in the beginning of code execution and this will be a monthly exercise going forward. What I need as a help is that when I run these codes in following months, how can I avoid manually effort to update each sql code, which is the current scenario right now i.e. currently the table is getting created as abc.sales_July2001, in the next month I would update the sql code to create table as abc.sales_August2011 and so on.

Yes, these tables are getting created initially and then every month going forward.

Let me know if I need to provide more information.

Thanks,
DILIPandey, Excel MVP
Enthusiast

Re: Dynamic Table Name

If the rest of the table definition remains the same, you could use BTEQ to generate and run a SQL script with the necessary table names. The snippet would look something like this:

.EXPORT REPORT FILE="file_to_run.sql";

SELECT 'CREATE TABLE abc.sales_'||CAST((CAST(CURRENT_DATE AS FORMAT 'MMMYYYY')AS VARCHAR(20)))||' AS abc.sales_template WITH NO DATA AND STATISTICS;' (TITLE '') FROM DBC.DBCInfo WHERE INFOKEY='RELEASE';

.EXPORT RESET;

.RUN FILE "file_to_be_run.sql";

Re: Dynamic Table Name

thanks Mnylin for your reply.

Though I am very new to Teradata and not aware of BTEQ, but would still try and understand the method suggested by you. If I get stuck at any point, I will get back to you. Many thanks for your help.

Regards,
DILIPandey
Dilip