Performance issues moving 1 million rows between tables

Question

I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this:

delete from table1 OUTPUT *.delete into table2

I am executing this job every hour, but it takes a long time.

Another problem is that my log file size is continuously increasing. How can I fix this?

Answer 1

Here are two really efficient solutions to this, since you're only moving data. These are efficient because they don't actually move data at all: they simply manipulate the metadata to present the data in the desired location. This means not only will they be fast, but the amount of logging required will be minimal.

1. If you're using Enterprise edition, take advantage of table partitioning. If there's only a single partition in a table, that partition is the table. Use ALTER TABLE ... SWITCH to "move" the data into table2.

2. Rename table1 to table2 using sp_rename, then recreate table1 using a SELECT TOP 0 * INTO statement, or by using the original table definition. Note that if there are any constraints/indexes/etc., you'll need to rename those, too, to avoid name clashes when the new table is created again. This method works with any edition, so I might actually recommend it over the other solution, which is Enterprise only.

Note that there are some restrictions to doing either of these -- see the documentation I've linked to. I'm not sure if you tagged the question with replication because the table is replicated (doesn't make much sense), or because you're trying to "replicate" the data (also doesn't make sense as you're wanting to move data, not make a copy, which is what replication implies).

Answer 2

you can use the BCP and BULK INSERT command and then truncate your source table

Answer 3

In SQL Server you can do it this way:

SELECT * 
INTO output
FROM source;

TRUNCATE TABLE source;