Using T-SQL to export Multiple Queries to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using T-SQL to export Multiple Queries to Excel

Author

Topic

squatchman74
Starting Member

9 Posts

Posted - 2009-03-13 : 14:38:10

Using the T-SQL command I would like to export Multiple Queries to One Excel Sheet. Using the Following command I can almost achive this:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\QuoteImport.xls;',
'SELECT Xfer, Prod FROM [Sheet1$]')
select cast(xrefer as nvarchar(255)) as 'Xfer', cast(sxprod as nvarchar(255)) as 'Prod'
from dbo.XREFER_ROWA
go

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\QuoteImport.xls;',
'SELECT XferA, ProdA FROM [Sheet1$]')
select cast(xrefer as nvarchar(255)) as 'XferA', cast(sxprod as nvarchar(255)) as 'ProdA'
from dbo.XREFER_ROWB
go

The Result I get is

1 xref1 prod1
2 xref2 prod2
3 xref3 prod3
4 xref4 prod4
5 xref5 prod5

What I need is

1 xref1 prod1 xref4 prod4
2 xref2 prod2 xref5 prod5
3 xref3 prod3

squatchman74
Starting Member

9 Posts

Posted - 2009-03-16 : 15:06:53

What I am looking for is something that answers this question:

how can i write two different query for same page in the excel.
i wrote two query and system displayed the results like this

1 2
--- ---
jan ---
jim ---
jon ---
jen ---
jun ---
--- MRC
--- BMW
--- FRD
--- RVR

but i want

1 2
--- ---
jan mrc
jim bmw
jon frd
jen rvr
jun

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:25:12

Is it a rule that A merges with F, B with G, every alphabet with the alphabet 5 positions ahead ?

squatchman74
Starting Member

9 Posts

Posted - 2009-03-16 : 17:42:04

In a nut shell, I am trying to export 2 seperate SQL queries to the same excel spread sheet. Query A may return 10 line or 100 lines. Query B may return 200 lines or more. When you use the openrowset command query A will start filling in the spreedsheet at A1 to A? depending on the result set. In a perfect world I would like to return Query B to D1 of the same sheet. What happens though is if Query A returns rows A1 to A100, Query B starts filling in at D101 not D1 like I need it to.

Where this really get intesting is when Query A and Query B combined return more than 65K rows I get a message that the spreadsheet is full when in reality if Query B would start at row 1 the would not be the case.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-17 : 00:19:26

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-03-17 : 05:59:13

moved from script library

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

squatchman74
Starting Member

9 Posts

Posted - 2009-03-17 : 10:23:16

I already looked through the post and on page 15 "frtslgn" posted the same question. It was never really answered and there was no more followup. The issue is that this is truely not a contatination.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 09:47:37

[code]
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\QuoteImport.xls;',
'SELECT Xfer, Prod,XferA, ProdA FROM [Sheet1$]')
SELECT m.Xfer,m.Prod,n.XferA,n.ProdA
FROM
(
select ROW_NUMBER() OVER (ORDER BY xrefer) AS Seq,
cast(xrefer as nvarchar(255)) as 'Xfer', cast(sxprod as nvarchar(255)) as 'Prod'
from dbo.XREFER_ROWA
)m
JOIN
(
select ROW_NUMBER() OVER (ORDER BY xrefer) AS Seq,
cast(xrefer as nvarchar(255)) as 'XferA',
cast(sxprod as nvarchar(255)) as 'ProdA'
from dbo.XREFER_ROWB
)n
ON n.Seq=m.Seq

go
[/code]

squatchman74
Starting Member

9 Posts

Posted - 2009-03-24 : 09:54:50

visakh16 this solution worked great for what I was trying to do. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources