Exporting SQL Server Table to multiple part files

sql sql-server database hive bcp
Jon picture Jon · Jul 13, 2013 · Viewed 21.6k times · Source

I need to export a fairly large SQL Server table ~100GB to a CSV file. But rather than the output be a single csv file, it should ideally be multiple files say 10 files each 10GB.

I see BCP has a batch_size argument but this still writes all data to a single file? Are there other free utilities for doing what I require? Either where the size of file can be specified in bytes or number of rows?

For bit of context this is so the data can be combined with other sources in a Hive/Hadoop platform, so if there are better ways of exporting the data I'm open for suggestions.

Answer

Hart CO picture Hart CO · Jul 14, 2013

I think you could use SQL 2012's paging functions OFFSET and FETCH in conjunction with bcp:

SELECT *
FROM Table
ORDER BY ID --Primary Key
OFFSET 100000001 ROWS
FETCH NEXT 100000000 ROWS ONLY

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More