Technical Article

Export table to file with bcp chunk

,

Export table to file with bcp chunk

DECLARE @imin BIGINT = (SELECT MIN([id]) FROM [YourDB].[dbo].[YourTable] (NOLOCK)),
  @imax BIGINT = (SELECT MAX([id]) FROM [YourDB].[dbo].[YourTable] (NOLOCK)),
  @sql VARCHAR(MAX),
  @tbl VARCHAR(1024) = 'TravelStatistic.dbo.[HotelRequestSupplierSearchResult]',
  @step BIGINT = 1000000000,
  @path VARCHAR(1024) = 'C:\Temp\',
  @bcpparams VARCHAR(256) = '-E -n -T -S ' + @@SERVERNAME,
  @maxdate VARCHAR(24),
  @runinsql BIT = 0 -- 0 Disable (only printed commands for manual runing into cmd)
  
  SELECT @maxdate = (SELECT CAST([Date] AS DATETIMEOFFSET) FROM [YourDB].[dbo].[YourTable] (NOLOCK) WHERE [id] = @imin+@step)
 WHILE @imin < @imax 
  BEGIN
  SELECT @sql = 'bcp "SELECT * FROM ' + @tbl + ' WITH (NOLOCK) WHERE id BETWEEN ' + CAST(@imin AS VARCHAR) + ' AND ' +CAST ((@imin + @step) AS VARCHAR) 
  + '" queryout ' + @path +  REPLACE(REPLACE(RIGHT(@tbl,CHARINDEX('.', REVERSE(@tbl)) - 1),'[',''),']','') + '_' + REPLACE(CAST(CAST(@maxdate AS DATE) AS VARCHAR),'-','_') + 'T' + LEFT(REPLACE(CAST(CAST(@maxdate AS TIME) AS VARCHAR),':',''),4) + '.dat ' + @bcpparams
  IF @runinsql = 0 
PRINT @sql
  ELSE 
  BEGIN 
SELECT @sql = 'master..xp_cmdshell ''' + @sql + ''''
PRINT @sql
EXEC(@sql) 
  END 
  SELECT @imin = @imin + @step
  SELECT @maxdate = (SELECT CAST([Date] AS DATETIMEOFFSET) FROM [YourDB].[dbo].[YourTable] (NOLOCK) WHERE [id] = @imin+@step)
  END

Rate

2.25 (4)

Share

Share

Rate

2.25 (4)