Is there any way I can copy the deleted records into a New table,
delete from Table where column '1'
IS POSSIBLE THAT I CAN DO SOMETHING LIKE THIS
delete from Table into Temptable where column '1'
I tired this in SQL but it did not work. It shows me this error: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'into'.
asked Feb 16 '10 at 03:01 PM in Default
You can capture the result after deletiong using below query.
create table #del (id int not null)
insert into #del select 1 insert into #del select 2 insert into #del select 3 insert into #del select 4
DECLARE @MyTab table ( ID int NOT NULL);
DELETE #del OUTPUT DELETED.id INTO @MyTab FROM #del d WHERE d.id BETWEEN 2 and 3;
SELECT Id FROM @MyTab GO
answered Feb 16 '10 at 03:56 PM
What about using the OUTPUT statement like this:
answered Feb 16 '10 at 03:53 PM
Grant Fritchey ♦♦
+1 to FatherJack, but just wanted to show the trigger syntax, which won't fit in a comment:
answered Feb 16 '10 at 03:25 PM
Matt Whitfield ♦♦
You can either do it in two stages within a transaction:
OR you can use a ON DELETE trigger that INSERTS the data into the new location as a reaction to the delete command that you execute.
The best option will depend on other factors - table size, index complexity, frequency of occurrence, etc
[Edit] The first option assumes this is a once only job and the new table doesnt exist. If its an ongoing requirement then you need something like
answered Feb 16 '10 at 03:12 PM