问题描述
我有一个表 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:
- 对于每个获得的Table_Name,我需要检索相应的Table_Column 值.
使用 Table_Name 和 Table_Column,创建一个新的 sql 查询,例如作为
- For every obtained Table_Name, I need to retrieve corresponding Table_Column value.
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;