October 30, 2007 at 10:25 am
Hi,
I'm having some issues with renaming my table. I actually have some code in a SSIS package and at the end a temp table is renamed. The new name of the table should be in the format 'file07_3' where value of 'file' does not change, 07 is the current year and 3 is the quarter. Can i use rename with datepart functions? I have tried it but it is not working. So far we have been doing this manually. Can someone please help.
Thanks in advance
October 30, 2007 at 10:39 am
Have you tried doing it with dynamic SQL?
October 30, 2007 at 10:48 am
Here is a simple example of adding a 4 digit year to the end of a table:
CREATE TABLE testing(ID INT)
DECLARE @Q VARCHAR(100)
SELECT @Q = 'sp_rename ''testing'', ''testing_' + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())) + ''''
SELECT @Q
EXECUTE (@Q)
October 30, 2007 at 10:48 am
What have you tried so far?
You need only execute the sp_rename stored proc as in (direct from SQL 2k BOL)
[font="Courier New"]EXEC sp_rename 'customers', 'custs'[/font]
which will rename the customers table to custs.
So you could do something such as (I haven't tried this)
[font="Courier New"]declare @oldName nvarchar(776), @newName sysname
set @oldName = 'MyTable'
set @newName = 'file' + cast(year(getdate() as char(4)) + '_' + cast(month(getDate() as char(2))
exec sp_rename @oldName, @newName[/font]
I haven't got your year/month formatting correct but the code ought to work.
October 30, 2007 at 2:29 pm
Thanks alot for your help..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply