How to move data from big table

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

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:"