时间戳

首页 > 解决方案 > 从多个表中选择一列 MSSQL

sql-server - 从多个表中选择一列 MSSQL

问题描述

简单的任务让我很难过。我想从不同的表中选择一列并将其插入结果表中。基本上，很像一个工会：

SELECT Email FROM TableA
UNION ALL
Select Email FROM TableB

等等...

但是，我想以自动方式执行此操作。正如我所说，看起来很简单，但我绊倒了它。我的代码尝试：

USE MyDatabase

IF OBJECT_ID ('TEMPDB..#Selection') IS NOT NULL DROP TableA #Selection;
SELECT Name AS TableA, ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS RowNumb
INTO #Selection
FROM Sys.TableAs AS T
WHERE NAME LIKE '%abc%'
ORDER BY 2


IF OBJECT_ID ('TEMPDB..#Result') IS NOT NULL DROP TableA #Result;
CREATE TableA #Result ( Email VARCHAR (200))

DECLARE @Counter INT 
SET @Counter = 1


WHILE @Counter <= ( SELECT MAX (RowNumb) FROM #Selection )

BEGIN

DECLARE @Table VARCHAR (100)
SET @Table = ( SELECT TableA FROM #Selection WHERE RowNumb = @Counter )

-- PRINT @Table SET @Counter = @Counter + 1 END

    INSERT INTO #Result
    SELECT Email
    FROM @Table

    SET @Counter = @Counter + 1
END

我相信有人会很快发现我的错误。非常感谢任何指导！

亲切的问候，M。

标签： sql-servertsqlsql-server-2008

解决方案

我会（个人）去追求更像这样的东西：

CREATE TABLE #Email (email nvarchar(200));

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'INSERT INTO #Email (Email)' + NCHAR(13) + NCHAR(10) +
           STUFF((SELECT NCHAR(13) + NCHAR(10) + 
                         N'UNION ALL' + NCHAR(13) + NCHAR(10) +
                         N'SELECT CONVERT(nvarchar(200),email)' + NCHAR(13) + NCHAR(10) +
                         N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                       JOIN sys.columns c ON t.object_id = c.object_id
                  WHERE c.[name] = N'Email'
                  FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,13,N'') + N';';
PRINT @SQL;
EXEC sp_executesql @SQL;

SELECT *
FROM #Email;

DROP TABLE #Email;

这将创建一个动态语句，该UNION ALL语句针对具有该列的每个表Email（在当前数据库中）创建一个查询，并将该值插入到所述临时表中。然后它从临时表中返回所述值（然后处理该表，因为我实际上不知道您将如何处理它）。

推荐阅读