How to move data from big table

Question

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

Answer 1

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

Answer 2

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 whole table with

Select * into SEM..Copy from Original

Truncate Original

Insert into Original Select * from SEM..Copy

If target server is SQL Server of the same version you can move the data by backup and restore.

In other cases take some bulkcopy option.

EDIT:

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

Answer 3

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.

Answer 4

I had to move a table with 4 billion rows to a different database on the same server using the same storage. There was not enough storage for two copies of the table so I had to move it a little at a time and clean up storage as I went. So I wrote this code. Note: It's better to have higher @IterationsToRun and lower @RecordsToCopy because this keeps transaction logs cleaner and makes it stop faster if you force a stop. Make sure you don't move so many records that you run out of disk space before you shrink the database.

    Declare @IterationsToRun int=3000
    Declare @RecordsToCopy BigInt=100000
    --
    -- To Manually stop this, open another query window and run the query below
    -- Update ##StopIterations Set StopNow=1
    --

    Declare @Iteration int=1

    Declare @MinId BigInt
    Declare @MaxId BigInt
    Declare @StopNow int

    IF OBJECT_ID('tempdb..##StopIterations') IS NOT NULL DROP TABLE ##StopIterations
    Select 0 as StopNow into ##StopIterations

    While @Iteration<=@IterationsToRun
    BEGIN
        Select @StopNow=StopNow from ##StopIterations

        IF @StopNow=0
        BEGIN
            Select @MinId=Min(<PrimaryKey>) From <OldDatabaseName>.dbo.<TableName>
            Select @MaxId=@MinId+@RecordsToCopy-1

            BEGIN TRY
                BEGIN TRANSACTION RecordInsert
                    Insert <NewDatabaseName>.dbo.<TableName>
                    Select 
                            <Column List excluding any Identities in the new table>
                    From <OldDatabaseName>.dbo.<TableName>
                    Where <PrimaryKey> Between @MinId and @MaxId

                    Delete From <OldDatabaseName>.dbo.<TableName> Where <PrimaryKey> Between @MinId and @MaxId
                COMMIT TRANSACTION RecordInsert
            END TRY
            BEGIN CATCH 
                IF (@@TRANCOUNT = 0)
                BEGIN
                    ROLLBACK TRANSACTION RecordInsert
                    PRINT 'Error detected, all changes reversed'
                END 
                SELECT
                    ERROR_NUMBER() AS ErrorNumber,
                    ERROR_SEVERITY() AS ErrorSeverity,
                    ERROR_STATE() AS ErrorState,
                    ERROR_PROCEDURE() AS ErrorProcedure,
                    ERROR_LINE() AS ErrorLine,
                    ERROR_MESSAGE() AS ErrorMessage
            END CATCH
        End
        Set @Iteration=@Iteration+1
    END
    IF OBJECT_ID('tempdb..##StopIterations') IS NOT NULL DROP TABLE ##StopIterations

    GO
    DBCC SHRINKDATABASE (<OldDatabaseName>,5)