SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Using T-SQL to export Multiple Queries to Excel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

squatchman74
Starting Member

9 Posts

Posted - 03/13/2009 :  14:38:10  Show Profile  Reply with Quote
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



Edited by - spirit1 on 03/17/2009 05:59:08

squatchman74
Starting Member

9 Posts

Posted - 03/16/2009 :  15:06:53  Show Profile  Reply with Quote
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

Edited by - squatchman74 on 03/20/2009 09:10:54
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 03/16/2009 :  17:25:12  Show Profile  Reply with Quote
Is it a rule that A merges with F, B with G, every alphabet with the alphabet 5 positions ahead ?
Go to Top of Page

squatchman74
Starting Member

9 Posts

Posted - 03/16/2009 :  17:42:04  Show Profile  Reply with Quote
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.
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
350 Posts

Posted - 03/17/2009 :  00:19:26  Show Profile  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Edited by - harlingtonthewizard on 03/17/2009 00:20:31
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/17/2009 :  05:59:13  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

squatchman74
Starting Member

9 Posts

Posted - 03/17/2009 :  10:23:16  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2009 :  09:47:37  Show Profile  Reply with Quote

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
Go to Top of Page

squatchman74
Starting Member

9 Posts

Posted - 03/24/2009 :  09:54:50  Show Profile  Reply with Quote
visakh16 this solution worked great for what I was trying to do. Thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000