Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is by far the best post for exporting to excel from SQL:</p> <p><a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926">http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926</a></p> <p>To quote from user <code>madhivanan</code>,</p> <p>Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel</p> <p>Create an Excel file named testing having the headers same as that of table columns and use these queries</p> <p>1 Export data to existing EXCEL file from SQL Server table</p> <pre><code>insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') select * from SQLServerTable </code></pre> <p>2 Export data from Excel to new SQL Server table</p> <pre><code>select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') </code></pre> <p>3 Export data from Excel to existing SQL Server table (edited)</p> <pre><code>Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [SheetName$]') </code></pre> <p>4 If you dont want to create an EXCEL file in advance and want to export data to it, use</p> <pre><code>EXEC sp_makewebtask @outputfile = 'd:\testing.xls', @query = 'Select * from Database_name..SQLServerTable', @colheaders =1, @FixedFont=0,@lastupdated=0,@resultstitle='Testing details' </code></pre> <p>(Now you can find the file with data in tabular format)</p> <p>5 To export data to new EXCEL file with heading(column names), create the following procedure</p> <pre><code>create procedure proc_generate_excel_with_columns ( @db_name varchar(100), @table_name varchar(100), @file_name varchar(100) ) as --Generate column names as a recordset declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100) select @columns=coalesce(@columns+',','')+column_name+' as '+column_name from information_schema.columns where table_name=@table_name select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''') --Create a dummy file to have actual data select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls' --Generate column names in the passed EXCEL file set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c''' exec(@sql) --Generate data in the dummy file set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c''' exec(@sql) --Copy dummy file to passed EXCEL file set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' &gt;&gt; "'+@file_name+'"''' exec(@sql) --Delete dummy file set @sql= 'exec master..xp_cmdshell ''del '+@data_file+'''' exec(@sql) </code></pre> <p>After creating the procedure, execute it by supplying database name, table name and file path:</p> <pre><code>EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path' </code></pre> <p>Its a whomping 29 pages but that is because others show various other ways as well as people asking questions just like this one on how to do it.</p> <p>Follow that thread entirely and look at the various questions people have asked and how they are solved. I picked up quite a bit of knowledge just skimming it and have used portions of it to get expected results.</p> <p><strong>To update single cells</strong></p> <p>A member also there Peter Larson posts the following: I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?</p> <p>This is the principle of how you do manage that</p> <pre><code>update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls;hdr=no', 'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99 </code></pre> <p>You can also add formulas to Excel using this:</p> <pre><code>update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls;hdr=no', 'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7' </code></pre> <p><strong>Exporting with column names using T-SQL</strong></p> <p>Member Mladen Prajdic also has a blog entry on how to do this <a href="http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx">here</a></p> <p>References: <a href="http://www.sqlteam.com">www.sqlteam.com</a> (btw this is an excellent blog / forum for anyone looking to get more out of SQL Server). For error referencing I used <a href="http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/">this</a></p> <h1>Errors that may occur</h1> <p>If you get the following error:</p> <blockquote> <p>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries</p> </blockquote> <p>Then run this:</p> <pre><code>sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POT-SQL: Export to new Excel file
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USrubStackOverflow
    1. USJonH
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POT-SQL: Export to new Excel file
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COExcept that the Jet driver apparently does not work on 64 bit systems, so you're back to square one.
      singulars
      1. PO
      1. USmickeyf
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload