How to export data to excel when it have data exceed million rows?

Transact-SQL
,
1

I work on SQL server 2012 I have select statement return 1.5 million rows so i need to export
these data to Excel file or more files
so how to do that

DECLARE @Export varchar(max) 

SET @Export = 
'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;HDR=YES;Database='+ @ExportFilePath +''',''SELECT * FROM [Sheet1$]'')

SELECT 
PartNumberC , 
CompanyNameC , 
PartNumberX,
CompanyNameX , 
[Status]  
from #TempReplacementImporter' ;

EXECUTE (@Export);
2

Try SSIS Package ..

Or

PowerShell ..

1.5 million rows into EXCEL .. means too much
Split it up into small bits

Good Luck

1 Like
3

this is good can you please tell me how

4

if use ssis how to do that

5

1- Please Google Serach
1- Please Google Serach850×681 13.5 KB
Please Google Serach
3 - Please google search
3 - Please google search1143×715 56.2 KB

6

Why?

1 Like
7

Is this a one-time operation or something you need to automate? If you need to automate the process then you should utilize SSIS or SSRS.

If the goal is to output to Excel - then you are limited on how many rows can be exported, and since you are still on SQL Server 2012 the output is XLS which is limited to 65,536 rows in each sheet.

If you have SSRS 2016 or higher, you can create a report that will output to an XLSX file - but that is also limited. XLSX files can have 1,048,576 rows per sheet...

If you can support a different file format - then I would recommend using SSIS to output a pipe-delimited file, or you can use Powershell to create the file. For the greatest control in Powershell you need to be on version 7 - which includes the ability to define the delimiters and how/when to use quotes.

1 Like
8

Why, on this good Green Earth, is anyone trying to export a million + rows to Excel to begin with? I'm not being sarcastic... I'm trying to be a bit practical. What's the end purpose of doing such a thing?

3 Likes
9

Jeff

I was thinking the same thing ..

Looks Un Usual