Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Unanswered: variables in table names

    Greetings,

    I am fairly new to SQL and need a bit of help. I don't know if this is possible but I'd like to be able to use a variable in a table name when creating a new table. I've tried the code below but I only get a table named '@tablename' (my variable name). I'm trying to assign a month + year to a new table name creating a list of monthly snapshots of a particular table.

    DECLARE @tablename VARCHAR(30)
    SET @tablename = 'patsnapshot' + str(datepart(month, CURRENT_TIMESTAMP) - 1)+ str(datepart(year, CURRENT_TIMESTAMP) - 1)
    SELECT *
    INTO [Warehouse].[dbo].[@tablename]
    FROM [Warehouse].[dbo].[patsnapshot]

    I you could let me know if/how this can be done it would be much appreciated. Thanks.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following:

    DECLARE @tablename VARCHAR(30)

    SET @tablename = 'patsnapshot' + ltrim(rtrim(str(datepart(month, CURRENT_TIMESTAMP) - 1)))+ rtrim(ltrim(str(datepart(year, CURRENT_TIMESTAMP) - 1)))

    exec ('SELECT * INTO warehouse.dbo.' + @tablename + ' FROM warehouse.dbo.patsnapshot')

  3. #3
    Join Date
    Jul 2002
    Posts
    2
    Awesome. Thanks a lot, that worked.


    Originally posted by rnealejr
    Try the following:

    DECLARE @tablename VARCHAR(30)

    SET @tablename = 'patsnapshot' + ltrim(rtrim(str(datepart(month, CURRENT_TIMESTAMP) - 1)))+ rtrim(ltrim(str(datepart(year, CURRENT_TIMESTAMP) - 1)))

    exec ('SELECT * INTO warehouse.dbo.' + @tablename + ' FROM warehouse.dbo.patsnapshot')

Posting Permissions

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