Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
Your next option (and probably best) is to use the export wizard. Right click on your database name, then Tasks, then Export Wizard
Your source will be the database you right clicked on.
Pick an excel destination.
Select the Write a query to specify the data to transfer.
Paste in your query
You can change the sheet name in your spreadsheet here.
Next is the list of mappings for you to review
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.
Then just hit finish and finish again.
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:
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).
