Skip to Content
Former Member
Sep 15, 2011 at 08:24 AM

Delete table in SQL command



As part of my report I populate a table then reference it in several following subreports.

The issue I'm having is that I need to clear all the data from my table after using it to allow the report to run again (and not get multiple results)

To do this I have tried to add a sub report at the very end (and start) of my report with the SQL command truncate table <tablename> or delete <tablename> or delete <tablename> where 1 = 1.

When I try truncate I get an error saying that I don't have permission (despite granting it but hey...) which is why I switched to delete, this runs fine but the table doesn't get deleted...

Any thoughts as to why this isn't working/how I can clear my table? I have thought of setting up a separate scheduled job to delete the table contents however I do also need to run the report several times with different parameters so this isn't an ideal solution.

I have tried adding a select after my delete statement as I thought if it had something to display on the report it would have to look at it but this didn't make any difference.

Any help will be much appreciated!