Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010

    Question Unanswered: How to output results to multiple files limitted by row count


    I'm trying to export a large number of records into .csv format for import into a 3rd party system. That in and of itself I can do. However, they have a max row count of 20,000 for reach file uploaded for import. We have a lot of many that just scrolling through them and highlighting/copying/pasting 20K records at a time would be unrealistic.

    basically what I need is to query all records in table X and then output the results in 19,999 row chunks into individual files...

    Is there a way to accomplish this with T-SQL?

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    There might be a way to do this, depending on the tables you're outputting. I would just export the tables to a single file then use a simple Perl/AWK/VB script to break that file into chunks that the import process can consume.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2005
    you could probably come up with a clever solution w/ bcp

    article on bcp:
    Page 4 - An Introduction To The Bulk Copy Utility

    post I made using ROW_NUMBER() OVER type syntax:

    the second link will not get you exactly what you want, but could be 1 step in getting the ID, if you have a bigint identity type PK with gaps, just change 5000 to 19999 and write a script to give you the bcp commands (including a unique file name), run query and then put results into a batch file to do all of the work for you from a CMD prompt.

  4. #4
    Join Date
    Jun 2005
    something like this...

    CREATE TABLE #MyBounds
      batchId int identity(1,1) primary key
    , startBound bigint
    , endBound bigint
    ; WITH OrderedIds AS
        SELECT Id,
        ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
        FROM dbo.MyTable
    SELECT Id 
    FROM OrderedIds
    WHERE RowNumber % 19999 = 0;
    	U1.startBound = U2.endBound + 1
    	#MyBounds U1
    JOIN	#MyBounds U2
    	WHERE	U1.batchId = U2.batchId + 1
    -- do the first batch manually then below will give you all subsequent batches
    SELECT 'bcp.exe |SELECT * FROM dbo.MyTable WHERE Id between ' + cast(startBound as nvarchar(10)) + ' and ' + cast(endBound as nvarchar(10)) + '| queryout |c:\myData_'+CAST(batchId as nvarchar(10))+'.bin| –n –T'
    FROM #MyBounds WHERE startBound IS NOT NULL
    -- search and replace | w/ "
    -- save file as bcp_my_data.bat
    -- either manually add script for 1st batch to file, or run first batch manually as a test
    -- tweak bcp script so it outputs as CSV (not sure of parameters)
    -- run final batch script after testing produces what you want
    Last edited by Gagnon; 10-27-10 at 18:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts