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 
    erid IN (
             distinct m.ID
             Member m
         left join 
                  *, row_number() over (PARTITION BY rid order by ceid desc) 
           as rn
              from TypeA) x 
             on m.ID = x.ID and x.rn = 1
                 (x.ceid is null and m.DateTime < @Deletion) 

     RETURN @intError
    RETURN 0

How can I copy the results of the select statement to a .txt file before deleting the records?

  • OUTPUT clause is one way, but I'm confused as to what you are doing here. Why the cursor? You are running the same DELETE over and over again and aren't using the cursor for anything other than a counter. Should it be where rid = @ID? If so, you still don't need a cursor for this. This RBAR method can be done in a single statement unless I'm missing something here. – scsimon Oct 22 '19 at 14:12
  • @ scsimon this was just an example. changed it, and as I mentioned in my question I need to use bcp to copy the deleted records into .txt file.. in the link you mentioned I can't find how to copy to file – Mindan Oct 22 '19 at 14:31

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

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.

| improve this answer | |
  • I see your point, but I don't know how to avoid the situation. Although I'm sure that I would run to such situation in some point – Mindan Oct 22 '19 at 18:19

As scsimon has pointed out, you can take advantage of OUTPUT clause instead of using a flat file.

CREATE TABLE MyTable(id int identity, foo int);
CREATE TABLE MyTableDeleted(id int, foo int);

    DELETED.* INTO MyTableDeleted
    foo <= 300;
id | foo
-: | --:
 4 | 400
 5 | 500
SELECT * FROM MyTableDeleted;
id | foo
-: | --:
 1 | 100
 2 | 200
 3 | 300
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.