My requirement was to totally redesign the table when i couldn’t just perform delete and update tasks on it. In this example i create new table based on old table’s filtered result set.
real life example:
My client had a CostumerDeals table with large amount of records in it, each time a costumer logged in to check his deals this large table was looped though. So my idea was to create a trigger (see schedule MSSQL query task) that will move (insert into new table and then delete) costumers deals which are older than 6 month to CostumerDeals_old table so the records amount decreased in 90% which made faster responses but also made me to program a trigger for removal of older than 6 month user deal and in case user returns to get his deal records back. It was time consuming but worth it.
code :
use dbName insert into tableA (colAA,colAB,colAC.....) select colBA,colBB,colBC.... from tableB where dateadd(mm,6,dealDate)<getdate()
Tags: sql