SQL Server Performance Forum – Threads Archive

Delete from multiple tables

Hello, I have more than 30 tables and I would like to delete records by running one query… delete from table where date =’6/6/2005′ every table has an ending with TER..for example aaaTER, bbbTER Can somebody help me with that Thanks

If you have common columns then create FKs with cascade delete option enabled.
This can be retained I beleive meaning FKs will protect your data integrity.
Other method would be to write a proc and dynamically build the delete stmt.

Run this Select ‘Delete from ‘+Table_Name + ‘ where date =”6/6/2005”’
from information_Schema.Tables where Table_Name like ‘%TER’
and Table_Type=’Base Table’ Copy the result back to Query Analyser and run them one by one Madhivanan Failing to plan is Planning to fail

Do you need this on a regular basis or this just a one time action? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Ensure database transaction log is not blown during this operation, so watch its space. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

thanks guys… I actually run this every month… Thanks Madhivanan, that was really helpful

]]>