Export Results of multiple dissimilar queries to Excel?

Question

I do not have SSRS/Visual Studio and I am running multiple queries in SSMS. The first Query provides an Overall Summary of all Data:

Select
'All Divisions',
Count(b.EmployeeID) As Numerator,
Count(a.EmployeeID) As Denominator,
Cast(Count(b.EmployeeID) As nvarchar) + ' / ' +     Cast(Count(a.EmployeeID) As nvarchar) As FinalRatio

From @tbl1 a Left Join @tbl2 b On a.EmployeeID = b.EmployeeID

The second query:

    Select
    a.Division,
    Count(b.EmployeeID) As Numerator,
    Count(a.EmployeeID) As Denominator,
    Cast(Count(b.EmployeeID) As nvarchar) + ' / ' +     Cast(Count(a.EmployeeID) As nvarchar) As FinalRatio
From
@tbl1 a Left Join 
@tbl2 b On a.EmployeeID = b.EmployeeID
Group By a.Division

... and the third one has all the details with no aggregation/summarization.

Is it possible to export all three result sets into one Excel spreadsheet in one operation - manually or some other way?

Answer 1

I would check to achieve this with OPENQUERY/OPENDATASOURCE/OPENROWSET.

The last two are probably more suited for excel than the first one, but I can't tell you which one is better.

To use this, you will need to activate ad hoc queries on the SQL instance, otherwise you will get and error message about ad hoc queries being disabled.

If you google this, you will find that most people use this to retrieve data from Excel, but pushing also work. To do so, write "insert/insert into" instead of "select from". You probably have a working example here.

Since you have three "select" statements, you will need to translate this into three "INSERT INTO OPENROWSET" statement. However, in order for this to work, the excel file must exists before hand otherwise OpenRowSet will fail to open because your need to specify a excel file and a spreadsheet. So just save an empty file on a networkshare that the SERVER where the sql instance is have access to.

Otherwise, to do it manually, just click the top left corner of the result grid in SSMS. This will select all resulted select. Then right-click copy and go paste it in your excel spreadsheet.