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'.
Answer by David 1 ·
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.
Answer by Matt Whitfield ·
+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]
Answer by Fatherjack ·
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
Answer by apat ·
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