Post reply

How to get data from a table whose name is in another table?

  • January 14, 2008 at 9:20 am

    #202341

    I have a tabel that contains the name of another table in its "TableName" column (a nvarchar type column). After getting the value from this TableName column, how can I use it to access that table?

    For example, if the TableName column contains xxxx, I want to be able to execute the SQL statement "SELECT * from dbo.xxxx". I tried this but got an "Incorrect Syntax" error.

    Is there a conversion required? If so, how?

    Thanks very much in advance for your help.

    sg2000

  • January 14, 2008 at 9:28 am

    #767716

    I think dynamic SQL is your only option to get round this somewhat dubious database design. Something like this:

    DECLARE @SQL varchar(1000)

    SELECT @SQL = 'SELECT * FROM ' + TableName

    FROM MyTableofTables

    WHERE

    EXEC (@SQL)

    You should read about using EXEC and sp_executesql, and also look up an article on the web that explains the pitfalls of dynamic SQL. It's called something like "The Curses and Blessings of Dynamic SQL" and I think it's by Erland Sommerskog.

    John

  • January 14, 2008 at 9:29 am

    #767717

    You can use Dynamic SQL.

    -Roy

  • January 14, 2008 at 11:38 am

    #767778

    Hi John and Roy:

    Yes, Dynamic SQL works for me! Thanks for the advice.

    sg2000

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

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