Delete and copy delete records into a new table

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 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

What about using the OUTPUT statement like this:

DELETE FROM MyTable
OUTPUT DELETED.* INTO MyOtherTable

+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]

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