Hi,
Is there any way I can copy the deleted records into a New table,
FOR EXAMPLE
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'.
Try this (assumes source and target tables have the same structure):
DELETE tbl1
OUTPUT deleted.* INTO tbl2
WHERE col = 'foo';
If the table structures are different you need to list the column names individually.
You can either do it in two stages within a transaction:
Begin tran COPYDEL Select mycolumns INTO mynewTable from myoldTable WHERE mycriteria = true; delete from myoldTable WHERE mycriteria = true; commit tran COPYDEL
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
Begin tran COPYDEL INSERT INTO mynewTable (mycolumns) Select mycolumns from myoldTable WHERE mycriteria = true; delete from myoldTable WHERE mycriteria = true; commit tran COPYDEL
+1 to FatherJack, but just wanted to show the trigger syntax, which won't fit in a comment:
CREATE TRIGGER [dbo].[myTrigger]
ON [dbo].[myTable]
FOR DELETE
AS
INSERT INTO [dbo].[myOtherTable] ([col1], [col2], [col3])
SELECT [col1], [col2], [col3] FROM [deleted]
What about using the OUTPUT statement like this:
DELETE FROM MyTable
OUTPUT DELETED.* INTO MyOtherTable
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
