Append SQL table name with today's date

Question

I understand that I can change a sql table using the follow sp:

EXEC sp_rename 'customers', 'custs'

How would I go about appending this so that the new table has today's date as a suffix?

I've attempt variations on the below theme with little success!!

EXEC sp_rename 'customers', 'customers +(CONVERT(VARCHAR(8),GETDATE(),3))'

Any help greatly appreciated.

Solution 1

This sounds like a very bad thing to do! you should evaluate your design, renaming your tables with dates in the names suggests that you will be spawning many tables, each for a different date. You could possibly add a date column into your table and use that to differentiate the data instead of creating completely new tables for different dates.

With that said, you can not have an expression as a parameter to a stored procedure in SQL Server. By attempting to concatenate the formatted date to the string 'customers', you were trying to pass an expression as a parameter.

you must store the expression in a local variable first, and then call the stored procedure with that local variable:

DECLARE @Value varchar(500)
SET @Value='customers' +(CONVERT(VARCHAR(8),GETDATE(),3))
EXEC sp_rename 'customers', @Value

Solution 2

You mean T-SQL, right? Move the functions outside of the single quotes. Something like:

EXEC sp_rename 'customers', 'customers' +(CONVERT(VARCHAR(8),GETDATE(),3))

Solution 3

DECLARE @TableName varchar(50)

SELECT @TableName = (SELECT 'Customers_' + convert(varchar(50),GetDate(),112))

EXEC sp_rename 'customers', @TableName

Comments

[Ricardo Deano]

I can't answer with a tick yet but that's spot on Jon..thank you very much.

[LoganS]

@Ricardo Deano - No problem make sure you accept the answer if it works.

[dburges]

I'd upvote you a million times if I could, this is generally a really really bad choice.

[KM.]

if you were going to recommend a date format, why not YYYYMMDD: customers20100408?

[Ricardo Deano]

Thanks for the advice KM, taken note of and I will most likely modify the design and implement something along the lines of what you have suggested i.e. an in table date stamp. My one concern with this is that this table will get big...very very big. In addition to this, my LINQ to SQL will have to be modified and I'm not sure at present how I can amend this so that only the latest 'customer' is taken i.e. based upon the date stamp. Hmmm...food for thought. You may see a few more questions coming soon!!

[dburges]

@KM, I fixed it, you are right the unambiguous date is better.

[KM.]

@8forty, if you have one table you can easily query based on Column='Value' with no drama over the table name, they way SQL was intended to be. Set up the proper index and it will be fast regardless of the number of rows. If you split the table up and rename them with the date in the name you will constantly be building dynamic SQL to query your data. You will get compile time failures when you build a table name that does not exist. It will be impossible to have foreign keys. You will have crazy UNIONS to access data that spans a day. Ask this as a real question and not a comment.

[KM.]

@8forty, you can easily access all data in a single table via a simple WHERE clause and a proper index. Otherwise you'll spam your schema with many duplicate tables for each day. I have no idea about your application, but in general this design is a bad idea. If you need to ask how to do it, I'd say it is a bad idea. Unless you really know what you are doing, I'd say it is a bad idea. Unless you have a very specific reason, I'd stick with my statement that it is a bad idea. If you what an honest assessment of your design, ask a specific question for your exact case and see the response...