1

I need to move all existing rows in table A over to table B. I also need to select ONLY the moved rows from table A. (Not from Table B since Table B is an archive and contains a lot of rows which will result in the select taking a lot of time)

I'm using Microsoft SQL Server 2008 and .Net (System.Data.SqlClient)

Please note that records are inserted continuously to Table A. I need to ensure only the moved records are selected and that they are selected from Table A before they are deleted.

What is the most efficient way to do this?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Harindaka
  • 4,658
  • 8
  • 43
  • 62

3 Answers3

3

Can't you do something like this:

BEGIN TRANSACTION;

SELECT * FROM source WITH (HOLDLOCK) 
WHERE ...

DELETE source
OUTPUT deleted.* INTO destination
WHERE ...;

COMMIT TRANSACTION;

Of course you wouldn't use SELECT * but I don't know your tables so...

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I think, no one will able to update the records of source table until the transaction completes. It means no new record insertion. – Romil Kumar Jain Jun 14 '12 at 12:34
  • @Romil That's a necessity given that the OP wants to do this for a discrete set of rows. Otherwise the source table could change mid-transaction. – JNK Jun 14 '12 at 12:38
  • To run this without transactions, one could output the deleted queries to a table variable, then select and insert these to the archive. – Stephan B Jun 14 '12 at 12:41
  • Stephan read my mind. This was what I finally did. See my answer below. Thanks Aaron for pointing me in the right direction. I hate locks ;) – Harindaka Jun 14 '12 at 12:46
1

To what I understand you would like to do something like this:

SELECT []
INTO archivetable
FROM activetable

SELECT []
FROM activetable
WHERE id in (SELECT id from archivetable)

DELETE
FROM activetable
WHERE id in (SELECT id from archivetable)

this will copy, select and delete in a somewhat safe way.

Stephan B
  • 3,671
  • 20
  • 33
  • it will be very slow to scan the archivetable because it may have billions of records. – Romil Kumar Jain Jun 14 '12 at 11:57
  • 1
    Concerning the second query, I do not want to involve the archive table in the select and delete queries as its huge and will slow them down considerably. Wont it? Any other way? – Harindaka Jun 14 '12 at 11:57
0

I was finally able to solve this issue by using the SQL Server's OUTPUT clause. Have a look below. IMO I see no reason why this method would hold any unnecessary locks either.

declare @TempTable TABLE(Col1 bigint, Col2 varchar(50))

delete from TableA
output Deleted.Col1, Deleted.Col2 INTO @TempTable

insert into TableB (Col1, Col2)
select Col1, Col2 from @TempTable

select Col1, Col2 from @TempTable

Thanks you all for your help.

Harindaka
  • 4,658
  • 8
  • 43
  • 62
  • This essentially does the same thing as my answer, doesn't it? Except mine selects first and doesn't involve an extra @table. – Aaron Bertrand Jun 14 '12 at 14:31
  • Yes almost except yours locks the table whereas this does not. – Harindaka Jun 14 '12 at 17:18
  • Don't you want this to be a blocking operation? – Aaron Bertrand Jun 14 '12 at 17:19
  • Nope. It doesn't have to be so. Besides blocking table A will result in complications since the frequency of rows being inserted to it is high and possibly done by multiple threads in my situation. – Harindaka Jun 14 '12 at 17:29
  • So you want to dump the whole table, and allow actions to continue occurring during the delete, but not delete any new rows that come in, and then the process that does the delete needs to know exactly which rows were deleted? Or is there really a where clause that is missing from your answer? If there is a where clause, are new rows that come into the table after the delete started going to match the same where clause? Or are you archiving old data and only inserting new data? – Aaron Bertrand Jun 14 '12 at 17:36
  • No theres no where clause and what you said about dumping the whole table is correct and yes inserts should be allowed during the delete and only the deleted ones moved and selected afterwards. I see no reason for blocking. See Stephan B's last comment on your answer. – Harindaka Jun 14 '12 at 17:42