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.

There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?

share|improve this question
    
big is relative...5-6mil doesn't sound so large to me. –  Derek Downey Oct 19 '11 at 21:58
    
@DTest I mean, it is according to performance of server (hardware) –  garik Oct 19 '11 at 22:09
2  
See this please stackoverflow.com/questions/7646715/… –  gbn Oct 20 '11 at 4:08
    
5-6 million rows is tiny even on the hardware of my mobile phone, sorry. My desktop nicely deals with 50 million row tables, and my server does 100 millin row updates in less than 5 minutes - and this is MY server (as consultant), not the one of my customers. –  TomTom Oct 20 '11 at 14:12

3 Answers 3

up vote 6 down vote accepted

Couple of additions to Rolando's suggestion.

If you're clearing out the old table, as well as populating new you could use the OUTPUT clause. Be mindful of the potential for log growth, consider a loop/batch approach if this may be a problem.

DELETE
    OldDatabase.dbo.MyTable
OUTPUT
    DELETED.col1
    , DELETED.col2
    , DELETED.col3
INTO
    NewDatabase.dbo.MyTable

BCP is a handy alternative to be aware of. Note this is using SQLCMD syntax.

:setvar SourceServer OldServer
:setvar SourceDatabase OldDatabase
:setvar DestinationServer NewServer
:setvar DestinationDatabase NewDatabase
:setvar BCPFilePath "C:\"

!!bcp "$(SourceDatabase).dbo.MyTable" FORMAT nul -S "$(SourceServer)" -T -n -q -f "$(BCPFilePath)MyTable.fmt"
!!bcp "SELECT * FROM $(SourceDatabase).dbo.MyTable WHERE col1=x AND col2=y" queryout "$(BCPFilePath)MyTable.dat" -S "$(SourceServer)" -T -q -f "$(BCPFilePath)MyTable.fmt" -> "$(BCPFilePath)MyTable.txt"
!!bcp "$(DestinationDatabase).dbo.MyTable" in $(BCPFilePath)MyTable.dat -S $(DestinationServer) -T -E -q -b 2500 -h "TABLOCK" -f $(BCPFilePath)MyTable.fmt
share|improve this answer
    
+1 bcp is a option. –  garik Oct 20 '11 at 8:10

If the TableA is really big, you can use bulk-operations and bcp utility

  1. export the data from TableA using bcp utility into file
  2. Create the TableB
  3. Import the data using bulk insert or openrowset (bulk...)
  4. delete old data from TableA
  5. rename the tables, if needed

see the About Bulk Import and Bulk Export Operations article.

share|improve this answer

For SQL-Server the real problem is avoiding the logging of the deleted rows.

My proposal is Set up a second database in simple recovery mode. Copy the the hole table with

Select * into SEM..Copy from Original

Truncate Original

Insert into Original Select * from SEM..Copy

If target Server Is SQL-Server of same revision you can move the data by backup and restore.

In other cases take some bulkcopy option.

EDIT:

Although it is time to learn about partioned tables, but that seems to be an option only for Enterprise Edition.

share|improve this answer
    
+1 you are right (edit), we are using sql server standard edition :( –  garik Oct 20 '11 at 8:07
    
+1. This may blow he log allocation there - not many databases are properly sizing their logs for scenarios like that. And a rollback with some million rows is SLOW. –  TomTom May 27 '13 at 13:47

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.