主页 / dba / 问题 / 6975
Accepted
garik
Asked: 2011-10-20 13:53:29 +0800 CST

如何从大表中移动数据

  • 772

有一个大表（5-6 百万条记录）。我必须将 90% 的旧记录移动到其他数据库（表）。解决方案？

sql-server sql-server-2008-r2

4 个回答

  1. Best Answer

    对 Rolando 的建议进行了一些补充。

    如果您要清除旧表并填充新表，则可以使用 OUTPUT 子句。请注意日志增长的潜力，如果这可能是一个问题，请考虑使用循环/批处理方法。

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

    BCP 是一个需要注意的方便替代方案。请注意，这是使用 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
    • 7

  2. 对于 SQL Server，真正的问题是避免记录已删除的行。

    我的建议是
    在简单恢复模式下设置第二个数据库。复制整个表格

    Select * into SEM..Copy from Original

Truncate Original

Insert into Original Select * from SEM..Copy

    如果目标服务器是相同版本的 SQL Server，您可以通过备份和恢复来移动数据。

    在其他情况下，采取一些批量复制选项。

    编辑：

    虽然现在是学习分区表的时候了，但这似乎只是企业版的一个选项。

    • 5

  3. 如果 TableA 真的很大，您可以使用批量操作和bcp 实用程序

    1. 使用 bcp 实用程序将 TableA 中的数据导出到文件中
    2. 创建表 B
    3. 使用批量插入或 openrowset (bulk...)导入数据
    4. 从 TableA 中删除旧数据
    5. 如果需要，重命名表

    请参阅关于批量导入和批量导出操作一文。

    • 3

  4. 我不得不将具有 40 亿行的表移动到使用相同存储的同一服务器上的不同数据库中。桌子的两份副本没有足够的存储空间，所以我不得不一次移动一点，边走边清理存储空间。所以我写了这段代码。注意：最好有更高的@IterationsToRun 和更低的@RecordsToCopy，因为这样可以保持事务日志更干净，并且如果你强制停止它会更快地停止。确保在收缩数据库之前不要移动太多的记录以致磁盘空间不足。

        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)
    • 1

相关问题