how to move deleted data in text file?

Question

 we delete data from table "Table1"

before delete data we move in "Table1Backup" table

then how to move same data in text file or excel sheet and save in particular folder

pls help me

Answer 1

SSIS package could take care of all of that, or just be used to query the Table1Backup and create the excel file.

---

Chuck

Answer 2

hi guys,

pls help me give some solution

i m helpless for this work

search lot of about it but did not get any solution

and i have no idea how to solve it

i m work on this from lot of time but finally i m blank

Thanks

Answer 3

hi Chuck Pedretti

actually we need to create dynamically a text file in paricular location 

when we delete a data from a table by query and

deleted data move in these text file

pls help me

thanks 

Chuck Pedretti

Answer 4

You can easily create text files dynamically with SSIS - you just use an expression for the filename and construct the expression to meet your path/filename requirements.

Good example of it here:

http://bi-polar23.blogspot.com/2008/02/dynamic-flat-file-destinations.html

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 5

using the OUTPUT clause for the DELETE statement you could save the deleted records in another database and than backup that database or export the table to a file.

for what reason do you need to store the deleted data in a text file and does it need to be in a readable form?

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 6

Anurag,

Let me restate your problem statement -

"When a user deletes data from a table using query window or a job, then make a copy of that data in a text file. This copy should be made for all delete commands that are fired on the table."

If this is correct then I can suggest two ways. Use whichever suits you better -

1. Create individual stored procedures to delete data from the tables.
   The stored procedure can accept parameters for the WHERE clause. In the first step, it will copy the data to an audit table and might also include few other elements like the user id of the person trying to delete data, host machine name, timestamp etc. In the next step, the stored proc will delete data from the original table.
2. Create ON DELETE triggers on the tables.
   Triggers use special table name 'deleted' that makes the deleted data available during the transaction. So technically, the ON DELETE trigger can do the same thing as the stored procedure mentioned above by moving the data from the internal 'deleted' table to the audit table.

After any of the above methods, schedule a SSIS or BCP job that exports the data from the audit table to a text file.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 7

You can try my code below. but I just want to make clear that I don't like to use the XP_CMDShell unless I have to.

Also you need proper permission to run XP_CMDSHELL and the permission for your output file path to write the file.

Begin Transaction

Truncate Table Table1Backup;

Delete From Table1		--Where Condition
Output Deleted.* into Table1Backup;

commit

exec XP_cmdshell 'SQLCMD -S YOURSERVERNAME -E -q "Select * From DATABASENAME.DBO.Table1Backup" -o "YourFilePath\mytest.csv"';