CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Andrew
Andrew

Reputation: 2539

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

Upvotes: 1

Views: 7026

Answers (6)

Der U
Der U

Reputation: 3314

I am using a similar cursor as @Pavel with a list of my indexes in order to reorganise them. Operations like this are one of the extremely few good reasons for cursors.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

In this respect, you can think of SQL as a compiled language like C/C++. The SQL statement is evaluated by a "compiler", and certain checks are done. One of those checks is for the existence (and permissions) for tables and columns referenced directly in the query. Exact table names must be present in your code at the time you build your query, so that the compiler can validate it.

The good news is that SQL is also a dynamic language. This means you can write a procedure to build a query as a string, and tell the database to execute that string using the EXEC command. At this point, all the same "compiler" rules apply, but since you were able to insert table names directly into your SQL string, the query will pass.

The problem is that this also has security implications. It would be a good idea to also check your table against a resource like information_schema.Tables, to avoid potential injection attacks. Unfortunately, if you're deleting whole tables your whole model may already be suspect, such that you can't guarantee that someone won't inject a table name that you really want to keep. But depending on how these are populated, you may also be just fine.

Upvotes: 3

Pavel
Pavel

Reputation: 1647

try using cursor :

DECLARE @tableName varchar(255)

DECLARE cur cursor for select tableName from tableNames where (...)

OPEN CUR
FETCH NEXT FROM cur into @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
exec('DELETE ' + @tableName)
FETCH NEXT FROM cur into @tableName 
END

CLOSE cur
DEALLOCATE cur

Upvotes: 3

John Dewey
John Dewey

Reputation: 7093

Assuming no potential constraint errors exist, one interesting possibility is an undocumented procedure sp_MSforeachtable, which will allow you to apply a given operation against all tables whose names are returned by your query:

EXEC sp_MSforeachtable @command1 = 'delete from ?'
, @whereand = 'and o.name IN (SELECT tableName FROM tablesNames WHERE ...)'

Also http://weblogs.asp.net/nunogomes/archive/2008/08/19/sql-server-undocumented-stored-procedure-sp-msforeachtable.aspx for more reading.

Upvotes: 1

Matthew
Matthew

Reputation: 10444

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.

Upvotes: 5

Geeky Guy
Geeky Guy

Reputation: 9399

The delete statement works with only one table name at a time.

The full syntax is documented here, but it's TL;DR... In short, you'll have to use the loop.

Upvotes: 0

Related Questions