Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Posts
    8

    Question Unanswered: Dynamic table name with getDate()

    I'm trying to work on an Integration Services project, and want to have a table copied into another table, but this destination table needs a datetimestamp in the name, like tbl061708547pm.

    I can get this to work, but if I try to use it with an into statement and a "+", I get an error telling me something is wrong around the "+":

    DECLARE @Date char(23)
    SET @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)
    SELECT colA, @Date FROM myTBL

    *****
    Error with:
    DECLARE @Date char(23)
    SET @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)
    SELECT * into @Date FROM myTBL

    ...and error with:
    DECLARE @Date char(23)
    SET @Date = CONVERT(char(23), GETDATE(), 14)
    SELECT * into 'myTBL_ + @Date FROM myTBL;

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why in the world are you making this so difficult for yourself?
    Why don't you save all this data into a single table with a datetime stamp?
    Applications which create tables or modify the database as part of their normal operation are almost always bad designs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As blindman observed, dynamically extending your schema is a symptom of a larger problem.

    Can you explain what you want to do (the why part of creating tables with dates in their names) so that we can help you find a better solution to the problem?

    -PatP

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    though not a good practice, as pointed out by others, still if u want a dynamic sql might work for u

    DECLARE @Date varchar(20)
    set @Date=replace(CONVERT(varchar(20), GETDATE(), 14),':','_')
    execute ('select * into myTBL_' + @Date + ' from myTBL')

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Two votes for "put the gun back into the locked box" and one for "I've loaded and cocked it for you - for your convenience, there's a round in the chamber".

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    Add another vote for "put the gun back into the locked box".

  7. #7
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    ok... thought it had a "handle with extreme care" tag....still "putting the gun back into the locked box" ...

Posting Permissions

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