Use a variable in a table name when renaming a table in SQL

Question

I am trying to rename a table in SQL Server 2008 R2 using the following code:

declare @date varchar(8)
set @date = convert( varchar(8), getdate(), 112)

exec sp_rename 'LM_SM_Billing_and_Send_Data', 'LM_SM_Billing_and_Send_Data_@date'

I intend to rename the table with the current date added.

select convert( varchar(8), getdate(), 112)

returns 20141219

but when I start renaming, it names the table;

LM_SM_Billing_and_Send_Data_@date  

instead of inserting a date

I'm wondering if you can rename it,

LM_SM_Billing_and_Send_Data_20141219

using a variable in the table name.

I searched Google a bit and everything seems to indicate the use of dynamic SQL, but I have never used it and am not sure what the syntax will be for getting the results I'm looking for.

Answer 1

, , , SQL . ( )

, , . .

exec sp_rename 'LM_SM_Billing_and_Send_Data', 'LM_SM_Billing_and_Send_Data_' + @date

, , , , . @myNewName , .

:

declare @date varchar(8)
set @date = convert( varchar(8), getdate(), 112)

declare @myNewName varchar(255) = 'LM_SM_Billing_and_Send_Data_' + @date

exec sp_rename 'LM_SM_Billing_and_Send_Data', @myNewName

, SQL: http://msdn.microsoft.com/en-us/library/ms189260(v=sql.105).aspx

Answer 2

Dynamic Sql .

select * into , .

, ,

declare @date varchar(8),
set @date = convert( varchar(8), getdate(), 112)


set @sql ='select * into LM_SM_Billing_and_Send_Data_'+@date+' 
           from   LM_SM_Billing_and_Send_Data'

exec sp_executesql @sql

Drop table LM_SM_Billing_and_Send_Data