CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Sadie
Sadie

Reputation: 63

use a variable in the table name when renaming a table in SQL

I am trying to rename a table in SQL Server 2008 R2 using the code below;

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'

My intention is to rename the table with the current date appended.

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

returns 20141219

but when I run the rename it names the table; 

LM_SM_Billing_and_Send_Data_@date

instead of inserting the date

I'm wondering if it is possible to have it rename to;

LM_SM_Billing_and_Send_Data_20141219

by using the variable in the table name.

I've been googling quite a bit and things seem to point to using dynamic SQL, but I've never used it and not sure what the syntax would be to get the results I am looking for.

Upvotes: 5

Views: 5414

Answers (2)

Dan
Dan

Reputation: 5231

The issue you've ran into here, as John Saunders pointed out in his comment, SQL won't substitute the value of your variable into your parameter. (String interpolation)

Another issue you might run into, when trying to work around this problem, is concatenating in the procedure call. The following will also not work.

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

The reason the above will also error, is because a parameter can be either a variable, or a constant, not an expression as the above shows. If we declare @myNewName, and set the desired value into that variable, we can then pass that to the procedure.

Give this a try:

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

This may be a good reference as one continues to work with SQL parameters: http://msdn.microsoft.com/en-us/library/ms189260(v=sql.105).aspx

Upvotes: 6

Pரதீப்
Pரதீப்

Reputation: 93704

Use Dynamic Sql to append the variable to the new table name.

Use select * into syntax to copy the data from new old table to new table this part has to be done dynamically.

Then finally drop the old table

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

Upvotes: 0

Related Questions