January 25, 2013 at 7:36 am
Hi. My name is Momba and I'm a T-SQL noob....
So I've got the following hard-coded query:
select * TableCurrent
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2007_2008
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2008_2009
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2009_2010
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2010_2011
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2011_2012
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
At the end of each fiscal year (June 30th) a new archive is made of the current table and I end up having to dig through everything to get what I need. And this is my best attempt to making it dynamic:
Use TableCurrent
Declare @loopYrbeg int
Declare @loopYrend int
Declare @tablename sysname
Declare @SQL varchar(MAX)
Set @loopYrbeg = 2007
begin
While @loopYrbeg < year( getdate() )
begin
Set @loopYrend = @loopYrbeg + 1
Set @tablename = 'Table_' + CONVERT(varchar(4), @loopYrbeg) + '_' + CONVERT(varchar(4), @loopYrend)
Set @sql = 'select *
from ' + @tablename +char(10)+
'WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)'+char(10)+
'union'+char(10)
Set @loopYrbeg = @loopYrbeg + 1
end
exec (@sql)
select * TableCurrent
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
end
...Unfortunately my best attempt doesn't work as it should. Please advise. Thank you.
January 25, 2013 at 8:04 am
This will fix what you have:
Declare @loopYrbeg int
Declare @loopYrend int
Declare @tablename sysname
Declare @SQL varchar(MAX)
Set @loopYrbeg = 2007
Set @sql = ''
begin
While @loopYrbeg < year( getdate() )
begin
Set @loopYrend = @loopYrbeg + 1
Set @tablename = 'Table_' + CONVERT(varchar(4), @loopYrbeg) + '_' + CONVERT(varchar(4), @loopYrend)
Set @sql = @sql + 'select * from ' + @tablename +char(10)+
'WHERE BRANCHID=''950'' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)' + char(10) +
'union' + char(10)
Set @loopYrbeg = @loopYrbeg + 1
end
set @SQL = left(@sql, LEN(@sql) - 6)
print (@sql)
end
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
January 25, 2013 at 8:34 am
Yes, this is getting really close (thank you)... but how do I union the current table with all the archives?
i.e.
select * TableCurrent
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630
...which doesn't have a fiscal year in its name.
January 25, 2013 at 8:52 am
Just initialize the string with that query, like so:
Declare @loopYrbeg int
Declare @loopYrend int
Declare @tablename sysname
Declare @SQL varchar(MAX)
Set @loopYrbeg = 2007
Set @sql = 'select * from tableCurrent ' + char(10) +
'WHERE BRANCHID=''950'' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)' + char(10) +
'union' + char(10);
While @loopYrbeg < year( getdate() )
begin
Set @loopYrend = @loopYrbeg + 1;
Set @tablename = 'Table_' + CONVERT(varchar(4), @loopYrbeg) + '_' + CONVERT(varchar(4), @loopYrend);
Set @sql = @sql + 'select * from ' + @tablename +char(10)+
'WHERE BRANCHID=''950'' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)' + char(10) +
'union' + char(10);
Set @loopYrbeg = @loopYrbeg + 1;
end
set @SQL = left(@sql, LEN(@sql) - 6);
print (@sql);
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
January 25, 2013 at 9:02 am
Ohhhhh (duh). That makes sense.
Thank you!!!! 😀
January 25, 2013 at 9:16 am
This:
declare @StartYear int = 2007,
@SQLCmd nvarchar(max);
with quickTally(n) as (select top(year(dateadd(mm,6,getdate())) - @StartYear - 1) n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))dt(n))
select
@SQLCmd = stuff((select char(13) + char(10) +
'select * from Table_' +
cast(@StartYear + n as varchar) + '_' + cast(@StartYear + n + 1 as varchar) +
' where branchid = ''950'' and (disc_dte is null or disc_dte > ''20070630'') union all'
from
quickTally
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'') +
char(13) + char(10) + 'select * from TableCurrent where branchid = ''950'' and (disc_dte is null or disc_dte > ''20070630'');'
;
print @SQLCmd;
January 25, 2013 at 9:20 am
Depending on how often you will be running this query you might consider using sp_executesql instead of the more generic exec. It will cache your execution plan and more importantly it allows for parameters.
http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2013 at 9:25 am
... I came across one last issue. How can I make the " While @loopYrbeg < year ( getdate() )" statement conditional?
More specifically IF month ( getdate() ) < 7
THEN @loopYrbeg < year ( getdate() ) - 1
ELSE @loopYrbeg < year ( getdate() )
January 25, 2013 at 9:31 am
Sean Lange (1/25/2013)
Depending on how often you will be running this query you might consider using sp_executesql instead of the more generic exec. It will cache your execution plan and more importantly it allows for parameters.http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]
This:
declare @StartYear int = 2007,
@SQLCmd nvarchar(max),
@params nvarchar(max);
set @params = N'@BranchID varchar(10), @DiscDate date');
with quickTally(n) as (select top(year(dateadd(mm,6,getdate())) - @StartYear - 1) n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))dt(n))
select
@SQLCmd = stuff((select char(13) + char(10) +
'select * from Table_' +
cast(@StartYear + n as varchar) + '_' + cast(@StartYear + n + 1 as varchar) +
' where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate) union all'
from
quickTally
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'') +
char(13) + char(10) + 'select * from TableCurrent where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate);'
;
print @SQLCmd;
exec sp_executesql @SQLCmd, @params = @params, @BatchID = '950', @DiscDate = '20070630';
January 25, 2013 at 9:31 am
momba (1/25/2013)
... I came across one last issue. How can I make the " While @loopYrbeg < year ( getdate() )" statement conditional?More specifically IF month ( getdate() ) < 7
THEN @loopYrbeg < year ( getdate() ) - 1
ELSE @loopYrbeg < year ( getdate() )
No loops required.
January 25, 2013 at 9:33 am
Sean Lange (1/25/2013)
Depending on how often you will be running this query you might consider using sp_executesql instead of the more generic exec. It will cache your execution plan and more importantly it allows for parameters.http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]
It has to run daily. I looked over some of the sp_executesql documentation and it does seem like a good fit for what I'm doing. There's just a learning curve on the syntax.
January 25, 2013 at 9:38 am
Wow, before I even post my reply you've answered my question.
(you're like a real Jedi knight)
...I'm going to try your method now.
January 25, 2013 at 9:53 am
momba (1/25/2013)
Sean Lange (1/25/2013)
Depending on how often you will be running this query you might consider using sp_executesql instead of the more generic exec. It will cache your execution plan and more importantly it allows for parameters.http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]
It has to run daily. I looked over some of the sp_executesql documentation and it does seem like a good fit for what I'm doing. There's just a learning curve on the syntax.
For what you are doing I am not sure you need to parameterize your sql. The syntax when using parameters is a little strange at first but once you do it a few times it gets easier. If you are not needing to parameterize your sql the syntax is almost identical. The following will get a list of all databases on your server. In your case the dynamic string would be more complicated but in the end it is just a select statement.
declare @sql nvarchar(max)
set @sql = 'select * from sys.databases order by name'
exec(@sql)
exec sp_executesql @sql
Check out this link from Gail's blog. It has an awesome example of using dynamic sql with parameters. I realize the topic is not relevant to your current situation but the article is well worth reading anyway. We all have to create these types of queries at some point. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2013 at 11:13 am
Sean Lange (1/25/2013)
momba (1/25/2013)
Sean Lange (1/25/2013)
Depending on how often you will be running this query you might consider using sp_executesql instead of the more generic exec. It will cache your execution plan and more importantly it allows for parameters.http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]
It has to run daily. I looked over some of the sp_executesql documentation and it does seem like a good fit for what I'm doing. There's just a learning curve on the syntax.
For what you are doing I am not sure you need to parameterize your sql. The syntax when using parameters is a little strange at first but once you do it a few times it gets easier. If you are not needing ...
The database has a lot of tables. The TableCurrent and Table_2007_2008, ..., Table_2011_2012 are just the few that I need.
January 25, 2013 at 11:18 am
Lynn Pettis (1/25/2013)
Sean Lange (1/25/2013)
Depending on how often you will be running this query you might consider using sp_executesql instead of the more generic exec. It will cache your execution plan and more importantly it allows for parameters.http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]
This:
declare @StartYear int = 2007,
@SQLCmd nvarchar(max),
@params nvarchar(max);
set @params = N'@BranchID varchar(10), @DiscDate date');
with quickTally(n) as (select top(year(dateadd(mm,6,getdate())) - @StartYear - 1) n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))dt(n))
select
@SQLCmd = stuff((select char(13) + char(10) +
'select * from Table_' +
cast(@StartYear + n as varchar) + '_' + cast(@StartYear + n + 1 as varchar) +
' where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate) union all'
from
quickTally
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'') +
char(13) + char(10) + 'select * from TableCurrent where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate);'
;
print @SQLCmd;
exec sp_executesql @SQLCmd, @params = @params, @BatchID = '950', @DiscDate = '20070630';
I got "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Table_2007_2008'." as an error message.
Also, on line "exec sp_executesql @SQLCmd, @params = @params, @BatchID = '950', @DiscDate = '20070630';" should that say @BranchID instead of @BatchID?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply