You can use dynamic SQL. See Re: Dynamic SQL Message #907449.

>I like to create a table having name related to the created day. For example,
>its name will be Survey20040920 if it is created today. Is it possible?
>
>I used following SQL, but it didn't work.
>
>
>Declare @Surveyfilename varchar(50)
>set @Surveyfilename= 'Survey'+CONVERT(char(8), getdate(), 112)
>
>select * into @Surveyfilename FROM [Survey]
>