I have created stored procedure where, I delete data in any given time. I want to save the records to a flat file before deleting in case I need to import the data back to table. I DO NOT want to call from the command line as I have a program that calls the stored procedure to schedule the job.
CREATE PROCEDURE [dbo].[Clean] ( @DeletionDate date ) AS DECLARE @id int, @intError int DELETE FROM Errn WHERE erid IN ( select distinct m.ID from Member m left join (select *, row_number() over (PARTITION BY rid order by ceid desc) as rn from TypeA) x on m.ID = x.ID and x.rn = 1 where ( (x.ceid is null and m.DateTime < @Deletion) ); ON_ERROR: RETURN @intError RETURN 0
How can I copy the results of the select statement to a .txt file before deleting the records?