11

In my sql server 2008, after running the query the data return is very huge say in millions. I tried copying but it gives an exception sql memory error. Can you let us know how can i copy the entire data and paste it into excel.

I don't want to export it to txt file as the data is not getting aligned. So i want to manually copy and paste it into excel. Please let me know the solution for thhis

1
  • As a point of clarification: You could be getting an "out of memory" exception when SSMS tries to handle the incoming data; if there's more than fits in memory, you'll get that error. If that doesn't cause the out of memory error, but it happens when you try to copy and paste the data, then the problem lies in its copying the data to memory a second time for the copy and paste. Any answer that involves bypassing the return of the data to SMSS (such as the current accepted answer) avoids this.
    – RDFozz
    Sep 9, 2018 at 14:26

3 Answers 3

20

You aren't going to be able to copy and paste like you want. However you do have several options. Simplest is to right click on the output and save as a CSV file. That assumes that you don't have an commas in your text data though.

Save Results As

Your next option (and probably best) is to use the export wizard. Right click on your database name, then Tasks, then Export Wizard

Where is the wizard?

Your source will be the database you right clicked on.

Source

Pick an excel destination.

Destination

Select the Write a query to specify the data to transfer.

Pick a query

Paste in your query

Paste in your query

You can change the sheet name in your spreadsheet here.

Select source tables

Next is the list of mappings for you to review

List of mappings

And from here you can either run the package immediately or save the SSIS package for later use/fixes. If your export doesn't work the first time and you are at all familiar with SSIS I would save the package so you can go into it and make corrections outside the wizard.

Save and run options

Then just hit finish and finish again.

0
2

If you insist on Excel as the destination, then there are two easy options.

First, the native "sqlcmd.exe" or "bcp.exe" allows you to run a query and will automatically output it to a file. Assuming your data has no tabs, the tab delimited output will automatically input into Excel columns. If your data does have tabs, you'll need to choose another delimiter and have Excel select columns based on that during the import.

Second, drop your query into an SSRS (SQL Server Reporting Services) report, run it, click the arrow to the right of the floppy disk/save icon, and export to Excel. Millions of rows requires SSRS 2012, saving as .xlsx - unfortunately, even SSRS 2008 R2 doesn't allow for more than 64k rows. To automate, schedule the report.

References for these commands:

Technet sqlcmd entry

Technet bcp entry

social.msdn sqlcmd tab delimited forum post

0

If it's just a one time thing.

Run the Query. In the results window at the bottom right click the Empty Square in the top left. Save results As... Default is a CSV, choose wherever you want to dump the file (network share or local).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.