Process Local Storage Tables

Toad Data Point
1

We use local storage to store our master tables for capacity reporting. These tables are appended to each month giving us a two year timesscale to trend across.

At the moment these tables are getting quite big so it would be good to trim old data out of them.

I have a delete query with a where statement simply saying ‘between A and B dates’

I have been trying to set up a loop data set to step through each table and delete any old data but I’m struggling with the correct syntax to use the loop dataset variable as a table name

Anyone already done this that can help?

2

Loop dataset variable (let’s say you’re using a default for its name) would be Loop_data_1_SQL. After Loop Dataset activity executes It contains a data set. You’re accessing a particular column with the dot notation. Let’s say your master query (the one that returns a list of tables you would like to truncate) returns those names in the TableName column. In this case you should use Loop_data_1_SQL.TableName to access a column with the tables name.

In this case the child sql (the one that is doing an actual table truncation) should be similar to this:

DELETE FROM #Loop_data_1_SQL.TableName# WHERE

Make sure you don’t use single quotes around #Loop_data_1_SQL.TableName#. In this case direct string substitution will take place when child Execute Script activity is about to execute:

DELETE FROM ActualTableName WHERE

I hope I answered exactly what you’d asked. If not, please clarify.

Igor.

3

Thanks Igor but I’m still getting an error message from TDP. My code is as follows

Loop Dataset contains:

SELECT MINITABLELIST.Name,

FROM TDP.MINITABLELIST

Execute script contains:

DELETE FROM #Loop_data_1_SQL.Name#

WHERE (‘DateTime’ BETWEEN date_format(curdate() - interval 36 month,’%Y-%m-01 00:00:00’)

AND date_format(last_day(curdate()-interval 25 month),’%Y-%m-%d 23:59:59’));

The ‘Name’ column comes from a copy of the table that is produced when you double click on the local storage database and the ‘DateTime’ column will always be in the target table - I’ve used the date parameters to import the same tables into a separate SQL database so I know they work.

The error is :

You have an error in your SQL syntax; check the manual that corresponds to your Local Storage server version for the right syntax to use near ‘FROM TDP.MINITABLELIST MINITABLELIST’ at line 2

any ideas why this isn’t working?

4

What type of quote do you use to quote DateTime column in the WHERE clause? For me they look like single quotes.

Use backquote ` instead.

Aleksey

5

Nice idea Aleksey but this made no difference whatsoever!

6

Aha! I have a solution. The problem I was having above was simply that I had an inappropriate comma in my select syntax (smacks head)!

In the delete syntax I’ve shortened it to say delete everything before the first day of 24 months ago:

DELETE FROM TDP.#Loop_data_1_SQL.Name#

WHERE (TDP.#Loop_data_1_SQL.Name#.DateTime < date_format(curdate() - interval 24 month,’%Y-%m-01 00:00:00’))

Please note the particular syntax - DELETE * FROM doesn’t work!

Also worth noting that if you use this the logs inform you of success but there is no data telling you how many rows you have deleted!

As with all delete queries USE WITH CARE!!