SQL Server Performance Forum – Threads Archive
Delete from multiple tablesHello, 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? —
Microsoft SQL Server MVP
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
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