SQL - concatenated table name

In this statement, I have to manually change the date of the table name everytime when I want to run this code:

 
SELECT *
INTO TRP.Cap_Data_20111007
FROM TRP.Cap_Data

Instead of manually changing it, can I do something like this?

DECLARE @yyyymmdd nvarchar(8)
SET @yyyymmdd = convert(varchar(8),getdate(),112)
 
SELECT *
INTO TRP.Cap_Data_ & '@yyyymmdd'
FROM TRP.Cap_Data

I'm not sure if there's a way to do this, but I'm not able to figure out how to. Please help.
HNA071252Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
same technique:
DECLARE @yyyymmdd nvarchar(8)
DECLARE @time nvarchar(4)
SET @yyyymmdd = convert(varchar(8),getdate(),112) 
SET @time = replace(convert(varchar(5), getdate(), 8), ':', '')

DECLARE @SQL nvarchar(1000)

SET @SQL = ' SELECT * 
INTO TRP.Cap_Data_' + @yyyymmdd + '_' + @time + '
FROM TRP.Cap_Data '
exec(@sql)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, dynamic sql would be the way
DECLARE @yyyymmdd nvarchar(8)
SET @yyyymmdd = convert(varchar(8),getdate(),112) 

DECLARE @SQL nvarchar(1000)

SET @SQL = ' SELECT * 
INTO TRP.Cap_Data_' + @yyyymmdd + '
FROM TRP.Cap_Data '
exec(@sql)

Open in new window

0
 
HNA071252Author Commented:
That's awesome! One more thing, what do I do if I need to add the time to the end of the table name?

i.e. 20111007_1525
0
 
HNA071252Author Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.