Exporting temporary table to excel spreadsheet

How would I go about programatically exporting all the data queried in a temporary table to an excel spreadsheet. My code is inside a stored procedure that builds a temporary table wiht the desired information. This is the table I am trying to export.

There is no easy way to do this like you've described above. Excel prefers to work with (non-temporary) tables and views. Is it possible to encapsulate your logic in a view?

Bryan

|||Yes, I could put all the final information in a view. Is this going to help?

I'm a new in SQL and I'm not familiar with all the intricacies of the environment.

Thanks for the help.

Lauran
|||

So, one thing you can do is start with Excel and see what it most easily can work with. If you establish a connection to your SQL Server database from Excel, you should get a list of all the objects it has access to. You should see a list of tables and views (if you have any in the database). Once you create your view, it will show up in that list.

If you're new to SQL, moving logic from a stored proc to a view can be tricky because many folks use stored procs to create a series of temporary tables to store intermediary result sets. Views do not support this technique and will force you to write your query as a single SELECT statement (which is a good thing from a learning perspective).

Good luck,
Bryan

|||That won't be a problem but what I'm really concerned here is the generation of the file.

The procedure I'm running is collecting data and is supposed to output it. It's a report. Now the people who need this report want to do the minimal amount of work, which is why I think that if they need to start up excel and select objects and such, it won't be a good solution.

What I want is a way of creating the excel file behind the scenes with a minimal amount of effort.|||

Ah! Now I'm with you.

Do you have Reporting Services installed? With Reporting Services, you can set up a report, have it rendered to Excel, schedule it to be placed on a file share or delivered via email to your consumers, etc.

You should stay clear of a multi-stepped stored proc if using SSRS.

Thanks,
Bryan

|||
Yes, we do have reporting services here. They're used with our more complex applications.

I am a Co-op student working here for 4 months. They asked this of me and left it in my hands - they didn't have any specific requirements. As long as they get that it's fine with them. I knew some SQL and I quickly drew up a solution for that. Personally I would like to make use of the reporting services but I don't know where to start.

Is using my already existing SQL code going to help or arriving at the report requires a different paradigm?

Any pointers?
|||

Try BCP.

You can either invoke it from a cmd window or use xp_cmdshell in SS.

Code Snippet

From cmd:

bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd

From SQL Server:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd'

Look up BCP in BOL for more options and parameters.

|||

Start by writing your code as a single SELECT statement. Open Business Intelligence Development Studio and create a new reporting services project. Set up a data source pointing to the database server. (Since SSRS is already in use, you may want to talk to some folks there about leveraging an existing shared data source.)

Add a report to your project by right-clicking the Reports folder in the Solution Explorer, selecting New Item, and then choosing Report. (Don't use the report wizard.)

On the DataSets tab, create a new data set leveraging the data source from above. Use your SELECT statement to return data.

Go to the layout tab and drag a table from the toolbox to the page. Open your data sets viewer (Ctrl+Alt+D if it isn't already open). Drag fields from your data set to the details row of the table. Go to the preview tab to verify the results.

So, at this point, you may want to spend a little time formatting, tweaking, etc. Once that is completed, deploy the report to your SSRS environment. You may need some help from the local admins to get everything set for this to work.

Now, locate your report in report manager. Open the report in the browser. You should see an "Select a format" drop-down. Select Excel from that drop down and click Export. When you get a pop-up dialog, select Open to view the report in Excel.

You may have a little back-and-forth to do to get the report as you want it in Excel. Books Online covers Excel rendering pretty well.

Once you have the report the way you want it, you can then set up a subscription to deliver the report to a file share or to an email address. When you set this up, you will have the option to select a format for the report file. You can use Excel.

There is a lot to learn in all of this. The BCP suggestion from above may get you where you want to go if you use a ".CSV" extension on the file b/c the extension is associated with Excel on most machines.

Good luck,
Bryan

|||
Thanks for your help today Bryan, it was most useful!

Thanks for your reply too Dale.

Have a great day.