Post reply

Renaming table with current year

  • October 30, 2007 at 10:25 am

    #183488

    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

    #746746

    Have you tried doing it with dynamic SQL?

  • October 30, 2007 at 10:48 am

    #746751

    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

    #746753

    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

    #746869

    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