Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table 18 Million records I need to add append it to another table (with the same structure) I tried the import-export wizard, the problem is that the log gets very very big and eventually it took down the full server, I had to kill the process in middle.

I think if I'll do it in batches it'll be faster, but I don't know how to do it.

(I don't need the old table, a record that has been copied could be deleted)

EDIT: bulk insert block all operations, is there a way to prevent him from blocking everyone here? enter image description here

share|improve this question
    
Are the source and destination tables in the same database? –  Thomas Stringer Apr 2 '12 at 23:02
    
Yes they are In the same database. –  Ezi Apr 3 '12 at 0:10
    
I'm going out on a limb here, but I'd be willing to bet you have underlying issues. What do you mean, "crash"?? Did your log file reach max size? Do you not have auto growth turned on (not a bad thing, unless you don't monitor log file consumption)? Did you actually run out of space in your partition? –  Thomas Stringer Apr 3 '12 at 2:05
    
My problem is that the server is heavy in use, so I have to watch not to impact the performance of the server. I see the log file getting bigger and bigger, it didn't complain but I didn't want to let it like that and eventually bring down all client's, so I killed it before. –  Ezi Apr 3 '12 at 15:19

2 Answers 2

up vote 6 down vote accepted
while (1=1)
begin
    delete top(10000) from oldtable
       output deleted.columnA, deleted.columnB, deleted.columnC
       into newtable (columnA, columnB, columnC)
    if @@ROW_COUNT = 0
       break;
end
share|improve this answer
    
Also, the OP should consider disabling the indexes and then rebuilding them once the data has been moved. No need to add the overhead of index inserts during this process. –  Thomas Stringer Apr 3 '12 at 0:55
1  
As the application can't be taken down I would assume that the destination table is already in production and can't have the indexes removed. –  mrdenny Apr 3 '12 at 2:27
    
Hey Remus, check the tags. Pretty sure that won't work for SQL 2005. :( –  mrdenny Apr 3 '12 at 2:28
2  
I stand corrected. I thought it was SQL 2008. If you guys would stop putting new features into the damn product I'd have less of this crap to remember. :) –  mrdenny Apr 3 '12 at 5:54
1  
Fist and formost I would check for blocking conflicts. Is feasible to add a WITH (READPAST) hint to skip locked rows, as the the rows skipped will be deleted on the next pass. Consider though that the loop may exit prematurely (after @@ROW_COUNT is 0 because all rows left were skipped). –  Remus Rusanu Apr 3 '12 at 15:39

The easiest way will probably be to open up BIDS and manually create a data transformation. Setup the destination for fast load (which will bulk insert the data). Then change the batch size to 1000. Then run the package.

share|improve this answer
2  
Never Mind, Remus's it easier. –  mrdenny Apr 3 '12 at 5:54
    
Please see the edit. I made a package with 100000 rows but it blocks all the query on the server. –  Ezi Apr 4 '12 at 18:44
    
You are waiting on disk to write to the disk, which is why the other queries are waiting for this one to finish as they can't read from the page which is being modified. –  mrdenny Apr 5 '12 at 2:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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