我在循环中运行以下一批语句,直到受影响的行数返回为0:
DECLARE @ScopeTable TABLE ( KeyField bigint, PRIMARY KEY ( KeyField ) )
INSERT INTO @ScopeTable
SELECT DISTINCT DocumentID FROM [CurrentArchive].[dbo].[ItemData]
WHERE DocumentID IS NOT NULL
INSERT INTO [CurrentArchive].[dbo].[ItemExtras]
SELECT TOP 1024 [ItemExtras].*
FROM [ItemExtras]
LEFT OUTER JOIN [CurrentArchive].[dbo].[ItemExtras] AS TargetTable
ON [ItemExtras].DocumentID = TargetTable.DocumentID
INNER JOIN @ScopeTable AS ScopeTable
ON [ItemExtras].[DocumentID] = ScopeTable.KeyField
WHERE (TargetTable.DocumentID IS NULL)
多年来,它在许多不同的数据库上都能正常工作,但突然在一个特定的数据库上,它返回38563行,这显然是"INSERT INTO@ScopeTable"部分的行数,因为另一个插入执行"TOP 1024"。
是否有某种SQL Server设置可以使其更改这样的行为?依赖于获得0是不合适的吗?
编辑:根据要求,下面是运行循环的(Delphi)代码。DataAccess.NewCommand使用TADOCommand,如果出现异常,则返回-1:
TopClause := '';
TopCount := 2048;
Finished := false;
while not Finished do
begin
AffectedRecords := fDataAccess.NewCommand(Format(
'%s INSERT INTO %s SELECT%s%s [%s].* FROM [%s] %s',
[PreClause, QualifiedTable, ManyToOneClause, TopClause, Table, Table, WhereClause]));
if AffectedRecords >= 0 then
begin
if TopClause = '' then // first time through
Finished := true
else if AffectedRecords < TopCount then // other times through
Finished := true;
end
else
begin
// if that fails, try top 1024, 512, etc until we reach 0 or it succeeds
TopCount := TopCount div 2;
if TopCount = 0 then
begin
fTracker.LastError := 'Failed to insert items for table ' + QualifiedTable;
raise Exception.Create(fTracker.LastError);
end;
TopClause := ' TOP ' + IntToStr(TopCount);
end;
end;
您可以尝试在第一次INSERT之前将NOCOUNT设置为on,然后将其重置回:
DECLARE @ScopeTable TABLE ( KeyField bigint, PRIMARY KEY ( KeyField ) )
SET NOCOUNT ON
INSERT INTO @ScopeTable
SELECT DISTINCT DocumentID FROM [CurrentArchive].[dbo].[ItemData]
WHERE DocumentID IS NOT NULL
SET NOCOUNT OFF
INSERT INTO [CurrentArchive].[dbo].[ItemExtras]
SELECT TOP 1024 [ItemExtras].*
FROM [ItemExtras]
LEFT OUTER JOIN [CurrentArchive].[dbo].[ItemExtras] AS TargetTable
ON [ItemExtras].DocumentID = TargetTable.DocumentID
INNER JOIN @ScopeTable AS ScopeTable
ON [ItemExtras].[DocumentID] = ScopeTable.KeyField
WHERE (TargetTable.DocumentID IS NULL)