Moving rows from one table to another

Question

I am moving records from one database to another, as a part of archiving process. I want to copy the rows to destination table and then delete the same rows from the source table.

My question is, what is the most efficient way to do a check if the first insert was successful before deleting the rows.

My idea is this, but I feel there is a better way:

@num_records=select count(ID) from Source_Table where (criteria for eligible rows)

insert * into Destination_Table where (criteria for eligible rows)

if ((select count(ID) from Destination_Table where (criteria) )=@numrecords)

delete * from Source_Table where (criteria)

Is it better/possible to combine it with RAISERROR function? Thank you!

Martin Smith · Answer 1 · 2013-01-04T21:37:01.973

19

If your archive table does not.

Have enabled triggers defined on it.
Participate on either side of a FOREIGN KEY constraint.
Have CHECK constraints or enabled rules.

You could also do it in one statenent.

DELETE FROM source_table
OUTPUT deleted.Foo,
       deleted.Bar,
       SYSUTCDATETIME()
INTO archive_table(Foo, Bar, archived)
WHERE  Foo = 1;

This will either succeed or fail as a unit and also avoids possible race conditions with rows being added in between the INSERT to the archive and DELETE (though your WHERE clause may well make this extremely unlikely anyway).

edited Jan 04 '13 at 21:37

answered Jan 04 '13 at 20:38

Martin Smith

84,644
15
245
333

None of the above. I suppose I could go that route, I like code minimalism. I just do not want to lose records if the insert fails for any reason. (ie: table locking, timeouts, etc) Thank you! – Dina Jan 04 '13 at 20:40
@Dina - Were you indicating that is possible with the OUTPUT clause? It isn't because it is all one statement. Also avoids issue of having to read the rows twice (and possibly losing rows that were added between the read for the insert and the read for the delete) – Martin Smith Jan 04 '13 at 21:43
Yes, that is what I meant. Thanks, I see your point. – Dina Jan 07 '13 at 15:27
FWIW - this method will cause logfile growth close to that of the size of the original table. Make sure you can live with that. If you cannot, break it into batches with DELETE TOP (N) and a While loop that checks the @@rowcount variable. – Wjdavis5 Mar 17 '16 at 02:38

score 13 · Accepted Answer · answered Jan 04 '13 at 20:14

I would recommend TRY/CATCH syntax along with explicit transactions. My assumption for this solution is that the reason the for the insert failure is some sort of trappable SQL error (such as a key violation, datatype mismatch/conversion error, etc.). The structure would look like this:

BEGIN TRAN

BEGIN TRY
  INSERT INTO foo(col_a,col_b,col_c,recdate)
  SELECT col_a,col_b,col_c,recdate
  FROM bar
  WHERE recdate BETWEEN @startdate AND @enddate

  DELETE FROM bar
  WHERE recdate BETWEEN @startdate AND @enddate

  COMMIT TRAN
END TRY
BEGIN CATCH
  ROLLBACK TRAN
END CATCH

The way this structure works, if any error occurs in the INSERT or the DELETE, the entire action gets rolled back. This guarantees that the entire action must be successful to be completed. If you felt that it was necessary, you could combine it with THROW for 2012 or RAISERROR in 2008 and previous to add additional logic and force a rollback if that logic wasn't met.

Another option is to look at SET XACT_ABORT ON, though I feel that the TRY/CATCH syntax gives you more granularity.

score 1 · Answer 3 · edited Jul 23 '15 at 14:15

Try this:

INSERT dbo.newtable(
      name,
      department,
      Salary
) SELECT 
            name,
            FirstName,
            Lastname
      FROM    (
           DELETE dbo.oldtable
           OUTPUT
                   DELETED.name,
                   DELETED.department,
                   DELETED.Salary
           WHERE ID  IN ( 1001, 1003, 1005 )
      ) AS RowsToMove;

SELECT * FROM dbo.newtable;
SELECT * FROM dbo.oldtable;

score 1 · Answer 4 · answered Jan 04 '13 at 20:18

The way I've thought about doing archiving (which I'm sure is not perfect either), is to add a bit column to the new archive table like 'Archived' which would have the value of 1 after successful transfer of a record. And once you transfer all the records, you can perform a delete operation while also looking for this 'Archived' field value of '1' i.e. True from the archived table.

And I agree with Mike on using Try/Catch.

Moving rows from one table to another

4 Answers4