asked
Delete Data from multiple tables
I want to delete data from many tables which is older than a year , Select Distinct 'C!' as Company, 'Table1' as SourceTable, 'date' as SourceDate, , (Select Cast(max(date) as date) from Table1) as calendar_Date UNION ALL Select Distinct 'C2' as Company, 'Table2' as SourceTable, 'date' as SourceDate, , (Select Cast(max(date) as date) from Table2) as calendar_Date ....continued like this.... Any suggestions how to do it....I don't want to write individual deletes since there are many tables....I want to delete everything where calendar_date < 1 year.....Thanks for your inputs in advance!
Is the date column used in the search criteria consistent throughout all of the tables? Meaning is the [Date] column the one used to determine if the record will be deleted or not? If so, you could do something like this: SELECT 'DELETE ' + QUOTENAME(SCHEMA_NAME([Schema_ID])) + '.' + QUOTENAME(name) + ' WHERE [Date] < DATEADD(YEAR,-1,GETDATE())' FROM sys.tables WHERE is_ms_shipped = 0 You won't have to type out all of the delete statements. Or you could use a cursor with a list of tables you want to touch and go that route. Hope that helps!
18 People are following this question.
