SQL Server BCP – How to Copy Table Content to Flat File Before Deleting

bcpsql serverstored-procedures

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?

Best Answer

If you want to write to a file, you can still use ouput.deleted with bcp queryout.

create table #mytable (id int)
insert into #mytable
values
(1),(2),(3)

create table ##deleted (id int)

delete from #mytable
output deleted.* into ##deleted
where id = 2


declare @cmd varchar(1000) = 'bcp "select id from ##deleted" queryout "E:\TEST\deleted.txt" -t, -c -T'
print @cmd
EXEC master..XP_CMDSHELL @cmd  

select * from #mytable
select * from ##deleted

drop table ##deleted, #mytable

I wouldn't do this before the delete... because what if the delete fails? You wouldn't want to log records as deleted when they actually didn't.

Related Question