本机SQL中大插入操作的批提交?

您可以对数据进行分区,并将数据插入游标循环中。这与SSIS批插入几乎相同。但在您的服务器上运行。

create cursor ....
select YEAR(DateCol), MONTH(DateCol) from whatever

while ....
    insert into yourtable(...)
    select * from whatever 
    where YEAR(DateCol) = year and MONTH(DateCol) = month
end

视图是否具有任何类型的唯一标识符/候选密钥?如果是这样,您可以使用以下命令将这些行选择到工作表中:

SELECT key_columns INTO dbo.temp FROM dbo.HugeView;

(如果有意义,可以使用简单的恢复模型将此表放入其他数据库中,以防止日志活动干扰主数据库。无论如何,这将生成更少的日志,并且您可以在恢复之前释放其他数据库中的空间,以防问题是周围的磁盘空间不足。)

然后,您可以执行类似的操作,一次插入10000行,并在两行之间备份日志:

SET NOCOUNT ON;

DECLARE
    @batchsize INT,
    @ctr INT,
    @rc INT;

SELECT
    @batchsize = 10000,
    @ctr = 0;

WHILE 1 = 1
BEGIN
    WITH x AS
    (
        SELECT key_column, rn = ROW_NUMBER() OVER (ORDER BY key_column)
        FROM dbo.temp
    )
    INSERT dbo.PrimaryTable(a, b, c, etc.)
        SELECT v.a, v.b, v.c, etc.
        FROM x
        INNER JOIN dbo.HugeView AS v
        ON v.key_column = x.key_column
        WHERE x.rn > @batchsize * @ctr
        AND x.rn <= @batchsize * (@ctr + 1);

    IF @@ROWCOUNT = 0
        BREAK;

    BACKUP LOG PrimaryDB TO DISK = 'C:\db.bak' WITH INIT;

    SET @ctr = @ctr + 1;
END

这都是我的想法,所以不要剪切/粘贴/运行,但我认为总的想法是存在的。有关更多详细信息(以及为什么我在循环中备份日志/检查点),请参阅上的这篇文章 sqlperformance.com :

• Break large delete operations into chunks

我知道这是一个旧线程,但我制作了Arthur光标解决方案的通用版本:

--Split a batch up into chunks using a cursor.
--This method can be used for most any large table with some modifications
--It could also be refined further with an @Day variable (for example)

DECLARE @Year INT
DECLARE @Month INT

DECLARE BatchingCursor CURSOR FOR
SELECT DISTINCT YEAR(<SomeDateField>),MONTH(<SomeDateField>)
FROM <Sometable>;


OPEN BatchingCursor;
FETCH NEXT FROM BatchingCursor INTO @Year, @Month;
WHILE @@FETCH_STATUS = 0
BEGIN

--All logic goes in here
--Any select statements from <Sometable> need to be suffixed with:
--WHERE Year(<SomeDateField>)=@Year AND Month(<SomeDateField>)=@Month   


  FETCH NEXT FROM BatchingCursor INTO @Year, @Month;
END;
CLOSE BatchingCursor;
DEALLOCATE BatchingCursor;
GO

这就解决了我们大桌子上的问题。

在不知道实际模式被传输的细节的情况下,通用解决方案将与您描述的完全相同:将处理划分为多个插入并跟踪键。这是一种伪代码T-SQL:

create table currentKeys (table sysname not null primary key, key sql_variant not null);
go

declare @keysInserted table (key sql_variant);
declare @key sql_variant;
begin transaction
do while (1=1)
begin
    select @key = key from currentKeys where table = '<target>';
    insert into <target> (...)
    output inserted.key into @keysInserted (key)
    select top (<batchsize>) ... from <source>
    where key > @key
    order by key;

    if (0 = @@rowcount)
       break; 

    update currentKeys 
    set key = (select max(key) from @keysInserted)
    where table = '<target>';
    commit;
    delete from @keysInserted;
    set @key = null;
    begin transaction;
end
commit

您可以使用BCP命令加载数据并使用Batch Size参数

http://msdn.microsoft.com/en-us/library/ms162802.aspx

• BCP将视图中的数据输出到文本文件中
• BCP将数据从文本文件导入具有“批处理大小”参数的表中

BCP