x

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

more ▼

asked Feb 16, 2010 at 03:01 PM in Default

InwoodGuy gravatar image

InwoodGuy
44 7 8 8

(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

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.

more ▼

answered Feb 16, 2010 at 03:52 PM

David 1 gravatar image

David 1
1.8k 1 3

That's funny. Got me by 29 seconds.
Feb 16, 2010 at 03:53 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Feb 16, 2010 at 03:56 PM

apat gravatar image

apat
29

(comments are locked)
10|1200 characters needed characters left

What about using the OUTPUT statement like this:

DELETE FROM MyTable OUTPUT DELETED.* INTO MyOtherTable 
more ▼

answered Feb 16, 2010 at 03:53 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

+1 Nice idea Grant!
Feb 16, 2010 at 04:02 PM Ian Roke
Thanks, but dportas had it first, by 29 seconds.
Feb 16, 2010 at 04:30 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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

answered Feb 16, 2010 at 03:25 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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

answered Feb 16, 2010 at 03:12 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

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.
Feb 16, 2010 at 05:51 PM David 1
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 ???
Feb 16, 2010 at 06:18 PM Fatherjack ♦♦
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.
Feb 17, 2010 at 10:43 AM David 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x985
x52

asked: Feb 16, 2010 at 03:01 PM

Seen: 7400 times

Last Updated: Feb 16, 2010 at 03:01 PM