2

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 '13 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? – KookieMonster Apr 29 '13 at 6:44
7

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

0

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. – dezso Apr 29 '13 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 '13 at 14:52
0

In SQL Server you can do it this way:

SELECT * 
INTO output
FROM source;

TRUNCATE TABLE 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 '13 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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