How do I send SQL query results to a spreadsheet?

I have developed code that produces what I need (see below).

How can I redirect this output into a single spreadsheet where each table is a tab in the spreadsheet and the tab is named after the table name?

Thanks!



This is the code that works:

***************** (begin code) ******************************
use QRMAssistant

DECLARE @TheTableName VARCHAR(100) -- Table name  

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM [QRMAssistant].[sys].[sysobjects]
Where xtype = 'u'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @TheTableName  

WHILE @@FETCH_STATUS = 0  
      BEGIN  
       
      SELECT t.TABLE_NAME as 'Table', c.COLUMN_NAME as 'Column Name' ,c.DATA_TYPE as 'Type' ,c.CHARACTER_MAXIMUM_LENGTH as 'Length',c.NUMERIC_PRECISION as 'Numeric length',c.NUMERIC_SCALE as 'Decimals',c.DATETIME_PRECISION as 'Date/Time'
      FROM INFORMATION_SCHEMA.Tables t
      INNER JOIN INFORMATION_SCHEMA.Columns c ON t.TABLE_CATALOG=c.TABLE_CATALOG AND t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME
      WHERE t.TABLE_NAME = @TheTableName
      ORDER BY t.TABLE_NAME, c.COLUMN_NAME


      FETCH NEXT FROM db_cursor INTO @TheTableName  

      END  

CLOSE db_cursor  
DEALLOCATE db_cursor

***************** (end code) ******************************
Lenny GrayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
I have a Perl script to do exactly this, but there's no way to do it easily with SSMS or any other tool I know of.  Will be interesting to see if anybody has a nice solution for this.
0
nemws1Database AdministratorCommented:
Since you're already looping through a cursor, you might be able to work up something with OPENROWSET:
INSERT INTO OPENROWSET 
   ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;','select * from [Sheet1$]')
SELECT * FROM dbo.whatevertable

Open in new window


You need to turn on 'Ad Hoc Distributed Queries' on your server in order for this to work, though.
0
David ToddSenior DBACommented:
Hi,

If SSMS is displaying the results in grid view, then its just a copy and paste.

Is this a one-off or a repeated thing? If repeated, then you could do this in SSIS with each result being an individual query that has a destination of a spreadsheet.

On the other hand, why not use excel and vba to execute the query and deliver the results to the relevant sheets.

Just gotta look at the problem crooked.

HTH
  David
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David ToddSenior DBACommented:
ps
In Excel VBA, Execute the query,
loop through the columns
use the fieldnames as headers.
Use the type to suggest formatting for the column.
next
Loop through the rows
Loop through the columns and copy the value into the cell
next
next
0
nemws1Database AdministratorCommented:
SSIS would be the preferred solution, but I haven't tried taking multiple results from a single call and tried to split them out to multiple sheets in Excel, but I would think its very do-able.
0
Lenny GrayAuthor Commented:
How do I save each table's columns to tabs in a worksheet where the tab name is the table name?
0
David ToddSenior DBACommented:
Hi,

As per what I said above about code in Excel - really easily.

If using SSIS then part of the connection to Excel specifies the tab. To use another tab you'll need another connection. But you'll need to make sure the first connection is closed before opening the second one as only one connection can write to an excel spreadsheet at one time.

Regards
   David
0
Lenny GrayAuthor Commented:
David -

Thanks for your assistance. I never coded in Excel VBA. Can you provide a template for me to mimic?

Thanks!
0
David ToddSenior DBACommented:
Hi,

Although it is querying an access database see these for starters
http://www.excel-vba.com/vba-code-2-12-SQL.htm
http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lenny GrayAuthor Commented:
Thanks, everyone, for the assist. Each of you provided the clues that I I use to generate the end result. When that happens, i will post the code here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.