Output query result to excel sheet

Question

Output query result to excel sheet

Rakesh.Chaudhary

SSC Eights!

Points: 901
More actions
July 30, 2013 at 7:29 am

#297019

Hi,
I want to put results into excel from Sql script , In results it is having three different result sets returned
Like as below.
Select * from table 1
Select * from table 2
Select * from table 3

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

easiest way is to use bcp.exe to a CSV file, which by default opens in Excel on most peoples machines.

if you want a single document, with three worksheets, you'll need to do something fancier; either via SSIS, or possibly with an existing excel document , via linked server, so you can overwrite the existing values on each sheet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rakesh.Chaudhary SSC Eights! Points: 901 More actions · Answer 2

Thanks !!

yes its good to use csv ,but can I use this cvs for creating multiple tabs also.

i.e.

I am having some value as @date

Select * from table1 where date=@date

Select * from table2 where date=@date

Select * from table3 where date=@date

Now using BCP command to output results into csv format and craete seprate tab for each dynamic Value @date

Chrissy321 SSCoach Points: 15750 More actions · Answer 3

No you can not use csv to create multiple tabs. You'll need to use one of the options that Lowell mentioned. A csv will always open in one tab.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

Rakesh.Chaudhary (7/30/2013)
Hi,
I want to put results into excel from Sql script , In results it is having three different result sets returned
Like as below.
Select * from table 1
Select * from table 2
Select * from table 3

You can do what you want (different tabs) using OPENROWSET. I've found that you can't write to the same Excel template multiple times consistently though. At some point, the thing loses track of what's going on. I've been able to work around that using a Windows scheduled job to simply replace the target Excel template before you re-populate it with the day's data.

Note that using OPENROWSET like this can be something of a pain in the nethers, but well worth the effort if the result you want is a nicely formatted spreadsheet containing the data you need.

I've heard but not tried that doing it through a linked server doesn't have this same issue.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St