0

I have a T-SQL query that returns over 1 million rows. I need to export the returned rows in separate .csv files with about 50000 rows in each.

Is there a way to automate this process?

Xiaoyu Lu
  • 3,280
  • 1
  • 22
  • 34
  • 2
    will the csv file be opened through excel. Excel 97-2007 only supports 65 536 rows, and Excel 2010 supports 1 048 536 rows. – Gauravsa Oct 16 '18 at 22:41
  • Are you using SSRS? It could be setup as a subscription as a .csv to a file server. – aduguid Oct 17 '18 at 00:09

1 Answers1

-2

I assume you already have a process to export the rows to csv. So all you need to do is add a batch number and then select the batch number in whatever query you are using to get your extract.

To get the batch number use

select <your columns>,
row_number() over(order by <maybe created date, PK, etc>) rn/50000 as batchNo
from <your table>

Then iterate through the batch numbers.

If your data is likely to change during the process then you could persist it to a temporary or staging table.

TomC
  • 2,759
  • 1
  • 7
  • 16