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

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

    Hi,

    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 records...so 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
    Location
    In front of the computer
    Posts
    15,579
    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.

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

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    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:
    http://www.dbforums.com/microsoft-sq...iteration.html

    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
    Posts
    319
    something like this...

    Code:
    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
    ) 
    INSERT
    	#MyBounds(endBound)
    SELECT Id 
    FROM OrderedIds
    WHERE RowNumber % 19999 = 0;
    
    UPDATE 
    	U1
    SET
    	U1.startBound = U2.endBound + 1
    FROM
    	#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
  •