move record from table1 to table2 THEN delete from table1.

[specialist]

Greetings Double T'ers-

I looked up and down in the forum for an answer to my question to no avail.

Alas, I am stuck with a fairly routine problem and hope to get some expert advice from you.

Here is my issue:

I would like to take a record in table, called 'table1' and move it to another table, called 'table2'

But wait, theres more...

After moving the record from table1 to table2, I would like to delete the record from table1.


It sounds easy, but I am having issues...nay... subscriptions trying to get this to work.

The purpose of the above statement is to delete the record from the database. However, as opposed to removing the record, I would rather move it to another table (so when someone says "Oops, I deleted the wrong record" I can have a method for recovering said record)

Crafty devil...

Any assistance would be so very wonderful. Thank you for reading this and considering helping me.

Have a great afternoon.

Sincerely,

specialist

 

[DrSql]

Try this..
--Pass the parameter, you where clause
declare @var char(6)
INSERT INTO table2
SELECT col1,col2,col3,col4
FROM table1
WHERE col1 = @var
delete table1 where col1=@var


Dr.Sql
Good Luck.

 

[mrdenny]

You could also use a trigger for this.

create trigger t_table1_d on table1
for delete
as
insert into table1_deleted
select *
from deleted

Every time someone deletes data the trigger will move the data to the deleted table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief