Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Red face Unanswered: Rename table to include date

    I'm trying to create a copy of TBL_CC_INPUT_TODAY (existing table)
    The copy need to have todays date appended to it,
    e.g. TBL_CC_INPUT_JUN20_2006

    This is what I have sofar but it won't work.
    Please help me


    select * into temp_table from TBL_CC_INPUT_TODAY
    declare @newname varchar(100)
    select @newname = 'TBL_CC_INPUT_'+convert(varchar(8),getdate()-1, 112)
    exec sp_rename temp_table, @newname

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi orla_d

    Welcome to the forum

    I'm afraid you have posted in the Access forum. The code you have supplied is T-SQL. You might get better joy if you repost in the SQL Server forum. A mod will then remove this post (so that answers are not duplicated).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I would also include more details about what doesn't work. Do you get errors? If so what? Is temp_table created and just not renamed? etc.

    BTW (2) - here is some T-SQL that creates a simple DDL CREATE TABLE statement on the fly. It won't create your indexes etc though.
    Code:
    DECLARE @ObjName as VarChar(100)
    DECLARE @CreateTable AS VarChar(2000)
    DECLARE @NewTName AS VarChar(50)
     
    SELECT @ObjName = 'TBL_CC_INPUT_TODAY'
    SELECT @CreateTable = ''
    SELECT @NewTName = @ObjName + '_' + convert(varchar(8),getdate()-1, 112) 
     
    SELECT @CreateTable = @CreateTable + ', ' + Column_Name + ' ' + Data_Type  + CASE WHEN Data_Type IN('VarChar', 'Char') THEN  '(' + CAST(Character_Maximum_Length AS Varchar(5)) + ')' ELSE '' END
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE Table_Name = @ObjName
     
    SELECT @CreateTable = 'CREATE TABLE ' + @NewTName + ' (' + SUBSTRING(@CreateTable, 3, LEN(@CreateTable)-2) + ')'
     
    PRINT @CreateTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2006
    Posts
    3
    Thanks for your help.


    I don't know anything about T-SQL and am fairly new to access so I'm getting lost sorry.

    I'll try to explain myself better.....
    It must be done through access. I have a macro that runs several queries. i want to add one query to it that will copy the final table (TBL_CC_INPUT_TODAY) and name this copy TBL_CC_INPUT_"today's date here" ... so that i have the date the query ran....

    any help would be greatly appreciated....

    Thanks again

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - ok. So this is all in Access? Where did the T-SQL code come from then?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Anyhoo - I don't do macroas but I don't think you can do this with one anyway. VB Code is what you need. Will post shortly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by orla_d
    Thanks for your help.


    I don't know anything about T-SQL and am fairly new to access so I'm getting lost sorry.

    I'll try to explain myself better.....
    It must be done through access. I have a macro that runs several queries. i want to add one query to it that will copy the final table (TBL_CC_INPUT_TODAY) and name this copy TBL_CC_INPUT_"today's date here" ... so that i have the date the query ran....

    any help would be greatly appreciated....

    Thanks again
    Why don't you store (append) all your data in a single table with a column for "date input" ? That way you can always retrieve all data for a given date and you won't have to create loads of tables. Plus the SQL is much easier

    Chris

  8. #8
    Join Date
    Jun 2006
    Posts
    3
    Pootle flump: The T-SQL was a section of code I modified as best I could to use for myself. VB code would be great thanks.

    Howey: I wanted to do it your way, and it used to be done that way, but the table got too big and slow after a few weeks......... can be anything up to 30,000 new records every monday (so they say).

    Thanks for the help guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •