从存储在表中的表/列名称构建查询

2021-09-10 00:00:00 sql tsql sql-server

我有一个表 TABLES_IDS 看起来像这样:

I have a table TABLES_IDS that looks like this:

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

我有一个第一次查询"，它返回多个 Table_Name 的值.这个Table_Name其实是另一个表的名字，而Table_Column是一列.我需要执行以下操作:

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. 对于每个获得的Table_Name，我需要检索相应的Table_Column 值.

  2. 使用 Table_Name 和 Table_Column，创建一个新的 sql 查询，例如作为

  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

  • 为第一个查询返回的每个 Table_Name 自动重复所有操作.

    推荐答案

    如果我理解您的问题，您想根据存储在此表中的表/列名称运行一系列查询.您不能将表名和列名作为变量或来自查询或表达式的结果来引用，因此您需要使用动态 SQL，例如

    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;

    • 相关文章