February 7, 2012 at 2:52 pm
hi
I have a query and it works when i hard code the column name in the select statement of the openquery.
Select
cast([pk_id] as int) fk_id,
[1978q1] intRate
from OpenQuery(Moodys_Linked,'Select * from [Sheet1$]')
output example
fk_id intRate
1 1.25
2 0.9
3 0.606
however, i have to get the intRate for each quarter for 57 years and i am trying to build the column name for the intRate ([Year + Quarter])
so I would have [1964Q1], [1964Q2],[1964Q3],[1964Q4],[1965Q1],.......... (NOTE: In the spreadsheet these represent the column names.
I have tried to to build a while loop to create these column names dynamically
declare @counter int
declare @count1 int
set @counter = 1964
set @count1 = 1
/** Insert data from moodys extract **/
while @count1 < 5
begin
while @counter < 2022
begin
Select
cast([pk_id] as int) fk_id,
cast ('[' + cast(@counter as varchar) + 'Q' + cast(@count1 as varchar) + ']' as varchar ) intRate
from OpenQuery(Moodys_Linked,'Select * from [Sheet1$]')
set @counter = @counter + 1
end
set @count1 = @count1 + 1
set @counter = 1964
end
[/code/
however, instead of creating the name in the spreadsheet and returning the values it is simply creating a varchar value for each record
i.e.
fk_id intRate
1 1978Q1
2 1978Q2
3 1978Q3
4 1978Q4
5 1979Q1
...............
Is it possible to create column name of a table or excel spreadsheet from scratch (i.e. dynamically)
or
what am i doing wrong
any help is greatly appreciated.
Thanks in advance
February 8, 2012 at 4:23 am
You have to use dynamic sql. This should work:
DECLARE @counter INT
DECLARE @count1 INT
DECLARE @cmd NVARCHAR(4000), @quarter VARCHAR(10)
SET @counter = 1964
SET @count1 = 1
/** Insert data from moodys extract **/
WHILE @count1 < 5
BEGIN
WHILE @counter < 2000
BEGIN
SELECT @quarter =CAST(@counter AS VARCHAR)+'Q' + CAST(@count1 AS CHAR(1))
SET @cmd = 'Select cast([pk_id] as int) fk_id, [' + @quarter + '] intRate
from OpenQuery(Moodys_Linked,''Select * from [Sheet1$]'')'
--Print @cmd
EXEC sp_executesql @cmd
SET @counter = @counter + 1
END
SET @count1 = @count1 + 1
SET @counter = 1964
END
[font="Verdana"]Markus Bohse[/font]
February 10, 2012 at 9:34 am
Thank you so much that was right on the money.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply