Query help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2007-11-19 : 16:36:59

Hi,

I have wrote a code which can retrive the data for 6 column for each ID.
Now there are 5000 ID in the system. And I have to retrive all the data for all those 5000 ID individually and create a report in excel.Thought it is a one time report , i have to copy and paste the results.

I have one clause in my query that WHERE ID=='2313'
Now for each time getting a differnt data for different ID , I have to change ID number manually. Can anybody tell me that wht is the best alternative for this ?

I am really stuck with this.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 16:52:13

if you are using sql 2005 an ssis would be a good solution to iterate over records, creating a new excel file for each.

jhon11
Starting Member

42 Posts

Posted - 2007-11-19 : 16:59:29

I am using SQL 2000

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 17:42:33

you can do this is dts, but it will be more difficult. another way is to use a distributed query in a loop. check out the link below, but here is an untested starting point to give you some ideas...

DECLARE @CMD varchar(4000)
DECLARE @ID int

SELECT @ID = MIN(ID)
FROM (SELECT 1 AS ID UNION ALL SELECT 2) AS FAKE_TABLE

while @ID is not null
begin
SELECT @CMD = 'copy c:\file_template.xls c:\file' + CONVERT(varchar(11), @ID) + '.xls'

EXEC dbo.xp_cmdshell @CMD

SELECT @CMD = ' INSERT INTO OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',''Data Source=c:\file' + CONVERT(varchar(11), @ID) + '.xls ; Extended Properties=Excel 8.0'' )...[Sheet1$]
SELECT ID FROM (FROM (SELECT 1 AS ID UNION ALL SELECT 2) AS FAKE_TABLE WHERE ID = ' + CONVERT(varchar(11), @ID)
EXEC (@CMD)

SELECT @ID = MIN(ID)
FROM (SELECT 1 AS ID UNION ALL SELECT 2) AS FAKE_TABLE
WHERE ID > @ID
end

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22908926.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources