how to write in excel file of sql query result

Question

i need to write certain sql result in excel file

like i run select * from products where name='xyz' when it fetch result it should write in excel file..

how can do this

Answer 1

Hi,

when it fetch result it should write in excel file..

First of all if you want to create a file which include XML structure, then you need that the query will return the result in XML format.

This can be done using the hint FOR XML. You can read more information and example in the following documnetation:

https://learn.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-ver15

Now, that your query returns the result as XML, we can discussion about passing the result to a new XML file.

There are tools which can do both steps for you

Option 1: Bulk exporting XML data using bcp unitality

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/examples-of-bulk-import-and-export-of-xml-documents-sql-server?view=sql-server-ver15#bulk_export_xml_data

Note: This is the proffered option probably

Option 2: using SSMS and FOR XML

Execute the query using FOR XML -> click the result -> this will open the result of the XML in new tab -> save the XML file

Note: this is the simplest option and most flexible since you can control the XML structure using the query

Option 3: Using Excel

Open Excel -> click the tab Data -> select get data -> from sql server

This will import the data directly to the excel

Next you can save the data as XML

https://support.microsoft.com/en-us/office/export-xml-data-0b21f51b-56d6-48f0-83d9-a89637cd4360

Note: this solution makes no sense for DBA but can fit people that do not familiar with the option FOR XML in queries and do not want to write any code

Answer 2

i just need to export multiple table using sql statements

Answer 3

i have below stuff an i write each result in excel sheet . i have an issue to write in excel sheet of each sql result

drop table #table_Northwind
create table #table_Northwind
(

RowID int not null identity(1,1) primary key,
column_name varchar(50)

)

insert into #table_Northwind (column_name)
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )

--select * from #table_Northwind
declare @Bluemchen _name nvarchar(50)
declare @i int
select @i = min(RowID) from #table_Northwind
declare @Max int
select @Max = max(RowID) from #table_Northwind

while @i <= @Max begin

SELECT  (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name,  [Scehma]=schema_name(o.schema_id), o.type   

FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%'

set @i = @i + 1
end