question

sql123456 avatar image
sql123456 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!
sqltablesdeletedateviews
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.