In MS SQL 2012 Database how to insert a very large table data in chunks

Question

I need to archive large chunks of information in SQL Server 2012 by moving the data to a new table without blocking other users from using the original table. Is the best way to do this using a T-SQL loop statement, or is there a better way to do it?

Answer 1

How to do it the way you're asking:

Create your second table, exactly like the first.

Find a column--something date-based, perhaps?--and use this to copy reasonable quantities of data into the second table. ("Reasonable" is an arbitrary value based on the size of the data and the amount of time it takes.) Then run a second query to delete the same data out of the first table. Be aware that the second query is a blocking query, meaning it will prevent anyone from using the table. In other words, the second query is the problem, not the first.

What I ended up doing was:

1. create temp table (id int identity, second column named after first table's PK and of the same or a compatible datatype).
2. select primary key where date between (range) into temp table
3. Loop through temp table (select the autonumber, delete from first table where PK = the one in the temp table).

This takes longer than a normal delete but doesn't block.

How I recommend doing it instead:

This is what I'm about to start doing:

1. Create new table, empty but otherwise exactly like old table.
2. Create job to rename old table (from data to data-201412) and rename new table with old name (from data-new to data).
3. Schedule it to run while I attend a New Year's party.

(Actually, I'm about to have a whole new database, but close enough.)