Dynamic From Clause

Hi,
Q: Is there a way to make the table's name in the from clause dynamic?
I have a job (T-SQL statements) that makes copies of current month's
selected tables to our archive DB. All copies will have a time stamp like
TableName_yyyy_mm. What I always do is that I edit the T-SQL statements in
that job to reflect the current year and the current month for all statement
s
(find and replace of course).
This month will be,
Select * into archive.dbo.PEIF_2005_03 from data.dbo.PEIF
Next month will be,
Select * into archive.dbo.PEIF_2005_04 from data.dbo.PEIF
.. and so on
I have tried using variables for the time stamp, like:
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
Select * into archive.dbo.PEIF_@.yyyy_@.mm from data.dbo.PEIF (DOESN'T WORK)
Apparently @.variable only works for where clauses (at least in my case).
What I want (if possible) is that I don't need to update the job every month
and let the job does the work for me.
Please help,
Adrian T> Q: Is there a way to make the table's name in the from clause dynamic?
http://www.sommarskog.se/dynamic_sql.html|||you would want to look into the stored procedure sp_executesql in BOL.
Basically it allows you to say "exec sp_executesql @.mySQL" and whatever
is in the string of @.MySQL you've just built up it will generate an
execution plan for it, then run it.
Cheers
Will|||ur solution is almost there.
try this.. a small change.. hope this helps.
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
exec('Select * into archive.dbo.PEIF_' + @.yyyy + '_' + @.mm + ' from
data.dbo.PEIF')