SQL Server – How to Copy Large Data from Query Result

sql server

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

Best Answer

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

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.

Related Solutions

Sql-server – Copying the database from SQL Server to PostgreSQL

For the person who's done this before I've looked at many tools and there are many pitfalls in doing that. The cheapest way I found to do this was to do a text dump, convert, and load.

My setup was MSSQL 2005 to PostgreSQL 8.x on Linux. The reason I say convert is because in the bulk bcp dump there is no way to distinguish between '' and NULL so the COPY command I used to load was complaining about NULL value for a table that had '' as a part of the primary key.

Dumping the schema also required conversion since the types are not exactly compatible, so it's been a bit of work but no buying of data migration tools which can be expensive for what may be just one time use.

There are other tools like this one but from what I have tested(2 years ago) has limitations.

Sql-server – How to Get the Sum (total) of a column in a query result set

Use the OVER clause to modify a SUM scope in your query. No GROUP BY needed

SELECT
    MyColumn, OtherColumn,
    SUM(MyColumn) OVER () AS SumTotal
FROM
    ...

Otherwise, SSMS isn't a calculating engine: it display query results so add it to your query...

Best Answer

Related Solutions

Sql-server – Copying the database from SQL Server to PostgreSQL

Sql-server – How to Get the Sum (total) of a column in a query result set

Related Question