从多个数据库合并一个表

Question

我有一个MS SQL 2005 (9.0.5000) Server，它有多个数据库，名为Mailing_XXXX，其中XXXX是一个序列号。我需要组合来自不同Mailing_XXXX数据库的多个表。问题是，我只能通过运行另一个XXXX来确定要使用哪个SQL statement。

因此，如果我运行这个T-SQL语句：

SELECT
    DatabaseName
FROM
    Config.dbo.Template
WHERE
    Name LIKE '%Mailing_%'

它将返回：

DatabaseName
Mailing_2491
Mailing_2663
Mailing_2664
Mailing_2666
Mailing_2667
Mailing_2670

因此，我需要创建一个组合表，其中包含每个Mailing_数据库的Mailing_表。

如果我手动编写这段代码，我可以很容易地做到：

SELECT * FROM Mailing_2491.dbo.PrimaryAddress
UNION ALL
SELECT * FROM Mailing_2663.dbo.PrimaryAddress
UNION ALL
   (etc etc)

然而，我需要这每天运行，因为新的邮件将不断被添加和删除。因此，我面临的主要挑战是如何处理变量Mailing_数。

Answer 1

DECLARE @sql varchar(max) = '';

SELECT @sql = @sql + ' UNION ALL SELECT * FROM ' + QUOTENAME(DatabaseName)
FROM Config.dbo.Template
WHERE Name LIKE '%BLAH%'

SET @sql = STUFF(@sql, 1, LEN(' UNION ALL'), '')
PRINT @sql;

EXEC (@sql);

Answer 2

这将获取所有数据库，并根据您的示例执行语句：

CREATE TABLE #dbs (id INT IDENTITY(1,1), dbname VARCHAR(255))

INSERT INTO #dbs
        ( dbname )
SELECT
    DatabaseName
FROM
    Config.dbo.Template
WHERE
    Name LIKE '%BLAH%'

DECLARE @sql NVARCHAR(MAX)
DECLARE @i INT = 1
DECLARE @dbcount INT

SELECT @dbcount = MAX(id) FROM #dbs

WHILE @i <= @dbcount
    BEGIN
        SELECT @sql = 'SELECT * FROM ' + dbname + ' UNION ALL'
    END

SELECT @sql = LEFT(@sql, LEN(@sql) - 10) --get rid of trailing union all
DROP TABLE #dbs

EXEC sys.sp_executesql @sql

从关系数据库的角度来看，拥有一个包含所有邮件信息的数据库和一个单独的表来区分“邮件号”将更有意义。