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.
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.
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';