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?

  • 3
    Unless I'm misreading the docs, that should be delete.* instead of *.delete. Please edit your question to fix this if that is the case. (Make sure the code/queries you post don't have typos before posting.)
    – Mat
    Apr 29, 2013 at 5:47
  • 1
    Are there any particular reasons why you have to do such a job and at this frequency? How often do you backup your logs? Is it possible to consider switching this database to bulk-logged recovery model? Apr 29, 2013 at 6:44

3 Answers 3


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


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

  • Could you elaborate on this a bit? Like why and how this would work, how it relates to other solutions? Probably adding proper formatting (code, capitals, punctuation marks) would also increase the value of your answer. Apr 29, 2013 at 13:27
  • @dezo, that information is easily avialble in Books Online. This answer is fine as it is. We don't need to spoon feed people.
    – HLGEM
    Apr 29, 2013 at 14:52

In SQL Server you can do it this way:

INTO output
FROM source;

  • 1
    This solution may not produce correct results unless transaction-level snapshot isolation is used, or it's guaranteed that no rows will be inserted into the source during this sequence.
    – Jon Seigel
    Apr 29, 2013 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.