Run a DELETE statement certain table names stored in a table
I have a table which stores the names of certain tables - tableNames. I'd like to run a DELETE statement on some of those tables (deleting all the rows from the tables they represent, not removing them from tableNames). I thought I could just do
DELETE FROM (SELECT tableName FROM tablesNames WHERE ...) AS deleteTables
But I keep getting an incorrect syntax error. I also thought about iterating through a table in a WHILE loop and storing using a variable, but that I'm hoping there's more simpler way. Specifically, this is for Microsoft SQL
You cannot do it that way because the inner SELECT is simply another set you're deleting from.
Basically you're creating a table of table names and telling the DB to delete it. Even iterating through them won't work without dynamic sql and EXEC
Do you need to automate this process?
What I've done in the past is something like this
SELECT 'DELETE ' + tableName FROM tablenames WHERE [conditions]
your output will look like this:
DELETE myTableName1 DELETE myTableName2 DELETE myTableName3
And then simply copying the results of this query out of the window and running them.
IF you need to automate this in SQL you can concatenate all the output strings in the result and send them as a parameter to an EXEC call.