将SQL Server 2008查询分为几批 [英] Break up a SQL Server 2008 query into batches
问题描述
我正在尝试准备一些数据以供第三方删除,但不幸的是,它们只能处理2000条记录中的数据.我有10万条记录,可能需要多次将此数据进行分割和导出,所以我想以某种方式使过程自动化.
I'm trying to prepare some data for deletion by a 3rd party, and unfortunately they can only process data in batches of 2000 records. I have 100k records and may need to divide-and-export this data several more times, so I'd like to automate the process somehow.
是否有使用SQL Server 2008的合理简便的方法?我没有运行复杂的查询-它与SELECT PKID FROM Sometable ORDER BY PKID
并不太远-虽然我可以使用游标进行此操作,但我想知道是否有更好的方法.
Is there a reasonably easy way to do this using SQL Server 2008? I'm not running a complex query -- it's not too far off from SELECT PKID FROM Sometable ORDER BY PKID
-- and while I can probably do this using a cursor, I'd like to know if there's a better way.
推荐答案
SET NOCOUNT ON;
CREATE TABLE [dbo].[SyncAudit] ( PkId INT, BatchNumber INT)
DECLARE @batchsize INT
,@rowcount INT
,@batchcount INT
,@rootdir VARCHAR(2048)
,@saveas VARCHAR(2048)
,@query VARCHAR(2048)
,@bcpquery VARCHAR(2048)
,@bcpconn VARCHAR(64)
,@bcpdelim VARCHAR(2)
SET @rootdir = '\\SERVER1\SHARE1\FOLDER\'
SET @batchsize = 2000
SET @bcpdelim = '|'
SET @bcpconn = '-T' -- Trusted
--SET @bcpconn = '-U <username> -P <password>' -- SQL authentication
SELECT @rowcount = COUNT(1),
@batchcount = CEILING(COUNT(1)/@batchsize) FROM <@TableName, string, 'SomeTable'>
SELECT [BatchSize] = @BatchSize, [BatchCount] = @Batchcount
INSERT INTO SyncAudit
SELECT
<@TableKey, string, 'PKField'>
,groupnum = NTILE(@batchcount) OVER ( ORDER BY <@TableKey, string, 'PKField'>)
FROM
<@TableName, string, 'SomeTable'>
WHILE (@batchcount > 0)
BEGIN
SET @saveas = @rootdir + 'batchnumber-' + cast(@batchcount as varchar) + '.txt'
SET @query = ' SELECT [<@TableName, string, 'SomeTable'>].*
FROM [' + db_name() + '].[dbo].[<@TableName, string, 'SomeTable'>]
JOIN [' + db_name() + '].[dbo].[SyncAudit]
ON [<@TableName, string, 'SomeTable'>].<@TableKey, string, 'PKField'> = [SyncAudit].PkId
AND [SyncAudit].BatchNumber = ' + cast(@batchcount as varchar) + ''
SET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveas + '" -c -t^' + @bcpdelim + ' ' + @bcpconn + ' -S ' + @@servername
EXEC master..xp_cmdshell @bcpquery
--EXEC (@query)
SET @batchcount = @batchcount -1
END
DROP TABLE [dbo].[SyncAudit] -- or leave for reference
这篇关于将SQL Server 2008查询分为几批的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!