Como mover dados de uma tabela grande

Algumas adições à sugestão de Rolando.

Se você estiver limpando a tabela antiga, bem como preenchendo a nova, poderá usar a cláusula OUTPUT. Esteja atento ao potencial de crescimento de log, considere uma abordagem de loop/lote se isso puder ser um problema.

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

BCP é uma alternativa útil para estar ciente. Observe que isso está usando a sintaxe SQLCMD.

: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

Para o SQL Server, o problema real é evitar o registro das linhas excluídas.

Minha proposta é
configurar um segundo banco de dados no modo de recuperação simples. Copie toda a tabela com

Select * into SEM..Copy from Original

Truncate Original

Insert into Original Select * from SEM..Copy

Se o servidor de destino for SQL Server da mesma versão, você poderá mover os dados por backup e restauração.

Em outros casos, escolha alguma opção de cópia em massa.

EDITAR:

Embora seja hora de aprender sobre tabelas particionadas, isso parece ser uma opção apenas para Enterprise Edition.

Se a TableA for muito grande, você pode usar operações em massa e o utilitário bcp

1. exporte os dados da TableA usando o utilitário bcp para o arquivo
2. Criar a TabelaB
3. Importe os dados usando inserção em massa ou openrowset (bulk...)
4. excluir dados antigos da TabelaA
5. renomeie as tabelas, se necessário

consulte o artigo Sobre operações de importação e exportação em massa .

Tive que mover uma tabela com 4 bilhões de linhas para um banco de dados diferente no mesmo servidor usando o mesmo armazenamento. Não havia armazenamento suficiente para duas cópias da mesa, então tive que movê-la um pouco de cada vez e limpar o armazenamento conforme avançava. Então eu escrevi este código. Observação: é melhor ter @IterationsToRun mais alto e @RecordsToCopy mais baixo porque isso mantém os logs de transação mais limpos e faz com que pare mais rápido se você forçar uma parada. Certifique-se de não mover tantos registros a ponto de ficar sem espaço em disco antes de reduzir o banco de dados.

    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)