SQL Server Performance

dynamic table name with getDate()

Discussion in 'SQL Server 2005 General Developer Questions' started by bubberz, Jun 17, 2008.

  1. bubberz New Member

    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. waqar Member

    Hi,
    You need to prepare statement and then use EXEC to execute it.
    SECTION 1:

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


    DECLARE @SQL VARCHAR(MAX)
    DECLARE @Date char(23)
    SET @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)
    SET @SQL='SELECT * into ' + @Date + ' FROM myTBL'
    EXEC(@SQL)


    SECTION 2:

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



    DECLARE @SQL VARCHAR(MAX)
    DECLARE @Date char(23)
    SET @Date = CONVERT(char(23), GETDATE(), 14)
    SET @SQL='SELECT * into
    myTBL_' + @Date + ' FROM myTBL'
    EXEC(@SQL)


    Just a suggestion, Why don't you keep Single decision making table and use extra field for date time stamp. Later you can index date time stamp field and filter data accordingly.
  3. Madhivanan Moderator

    <<

    Just a suggestion, Why don't you keep Single decision making table and use extra field for date time stamp.
    >>
    This is what I suggested to OP in the other Forum
  4. Madhivanan Moderator

Share This Page