question

InwoodGuy avatar image
InwoodGuy asked

Delete and copy delete records into a new table

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

sql-server-2005t-sqldelete
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
That's funny. Got me by 29 seconds.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image David 1 commented ·
Be careful! Unless you are using SERIALIZABLE isolation you may delete something which you have not copied. DELETE with OUTPUT is a better method for that reason.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
As I understand it, within *this transaction* changes could be made to cause 'phantom' records but other transactions would be waiting. Therefore, unless some step is introduced in this script to alter data between the INSERT and the DELETE then this should work. Unless I have misunderstood the READ COMMITTED section here : http://msdn.microsoft.com/en-us/library/ms173763.aspx ???
0 Likes 0 ·
David 1 avatar image David 1 commented ·
But if new rows are inserted on another connection between the INSERT and DELETE then they will get deleted but not copied to the new table. To avoid that use SERIALIZABLE isolation or a TABLOCK and HOLDLOCK hint.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

What about using the OUTPUT statement like this:

DELETE FROM MyTable
OUTPUT DELETED.* INTO MyOtherTable
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image Ian Roke commented ·
+1 Nice idea Grant!
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Thanks, but dportas had it first, by 29 seconds.
0 Likes 0 ·
apat avatar image
apat answered

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.