CODEWITHSUNDEEP
sqlsql-servert-sql
onkami
onkami

Reputation: 9411

Build queries from table/column names stored in a table

I have a table TABLES_IDS that looks like this:

Table_ID  Table_Name  Table_Column
--------  ----------  ------------
100       Attributes  Attribute_id
101       NewSamples  Section_id
...

I have a "first query" that returns several values of Table_Name. This Table_Name is actually the name of another table, and Table_Column is a column. I need to do the following:

  1. For every obtained Table_Name, I need to retrieve corresponding Table_Column value.

  2. Using Table_Name and Table_Column, create a new sql query that looks e.g. as

    SELECT FROM Table_Name WHERE Table_Column = 12345

  3. Automatically repeat everyting for every Table_Name returned by the very first query.

Upvotes: 1

Views: 1828

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

If I understand your question, you want to run a series of queries based on the table / column name stored in this table. You can't reference table and column names as variables or coming from the result of a query or expression, so you need to use dynamic SQL, e.g.

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'SELECT * FROM dbo.' 
  + QUOTENAME(Table_Name) + ' WHERE ' 
  + QUOTENAME(Table_Column) + ' = 12345;'
FROM dbo.TABLES_IDS
-- WHERE...
;

EXEC sp_executesql @sql;

Upvotes: 4

Related Questions