July 12, 2010 at 9:03 am
I am pulling data from 12 different columns each based on a month, e.g. Filename1, Filename2, . . . Filename12. I want to pull only from the file for the current month. Does anyone have any ideas? I am sure I need to make the month number a variable, but I am not sure how to put it all together. Any assistance would be greatly appreciated.
July 12, 2010 at 9:19 am
You can use dynamic sql, although there are issues with this as well. You need to specifically grant select rights to each table.
create table t1 (ID int, Quantity int,
Field1 int, Field2 int)
insert into t1
select 1, 2, 11, 12 union all
select 1, 3, 110, 120 union all
select 1, 4, 111, 121
create table t2 (ID int, Quantity int,
Field1 int, Field2 int)
insert into t2
select 2, 5, 112, 122 union all
select 2, 6, 113, 123
declare @vs_syntax nvarchar(max), @vn_table tinyint
-- retrieve records from t1
set @vn_table = 1
set @vs_syntax = 'select id, Quantity, Field1, Field2 from t' + CAST(@vn_table as varchar)
exec sp_executesql @vs_Syntax
-- retrieve records from t2
set @vn_table = 2
set @vs_syntax = 'select id, Quantity, Field1, Field2 from t' + CAST(@vn_table as varchar)
exec sp_executesql @vs_Syntax
drop table t1
drop table t2
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 12, 2010 at 9:32 am
Thank you very much! That worked. I was on the right track, except I forgot to convert the integer to a string.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply