Post reply

Create a table name from 2 or more strings

  • June 3, 2014 at 8:38 am

    #99054

    Hi,

    Is it possible to create a table name from 2 or more strings.

    A simple version of what I am trying to do it:

    Select

    Year(GetDate())

    ,Month(GetDate())

    ,Manufacturer

    From

    dbo.DW_+ Right('0' + Cast(Month(GetDate()) As VarChar(2)),2) + Right(Cast(Year(GetDate()) As VarChar(2)),2)

    Where

    Asset_Status = 'Live'

    but I keep getting syntax errors near the '+'

    It's probably a simple answer but I can't see it.

    Any ideas

    Stuart.

  • June 3, 2014 at 8:48 am

    #1718568

    not directly like that.

    you have to switch to dynamic SQL to convert value s(ie a column value) to the metadata of an object name

    your query qould create this code, is that the expectation?

    Select

    Year(GetDate())

    ,Month(GetDate())

    ,Manufacturer

    From

    dbo.DW_0614

    Where

    Asset_Status = 'Live' ;

    the dynamic SQL:

    DECLARE @cmd varchar(max)

    SELECT @cmd='

    Select

    Year(GetDate())

    ,Month(GetDate())

    ,Manufacturer

    From

    dbo.DW_' + Right('0' + Cast(Month(GetDate()) As VarChar(2)),2) + Right(Cast(Year(GetDate()) As VarChar(4)),2)

    +'

    Where

    Asset_Status = ''Live'' ;'

    print @cmd

    EXEC(@cmd)

    select Right(Cast(Year(GetDate()) As VarChar(4)),2)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 3, 2014 at 9:05 am

    #1718574

    Brilliant. That's exactly what I wanted.

    Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply