时间戳

首页 > 解决方案 > 每列连接数据库，其中数据位于不同的数据库中

sql - 每列连接数据库，其中数据位于不同的数据库中

问题描述

我正在尝试从位于不同数据库中但具有相同架构的表中获取数据。

IE 数据库名称类似于：DB1、DB2、DB3。

它们都包含表'MyTable'并且有一个外键匹配数据库中的'MainTable'主键，我们将称之为MainDB，我只是做一个联合并担心稍后的性能，但问题是，在这种特殊情况下，有时 DB3（或其中任何一个）不存在，这会导致我的查询失败并且需要每天进行编辑。

MainTable 的列在组合时构成了我需要在每列上加入的数据库的名称，为简单起见，我将其称为名为 ForeignDBName 的列。我想要类似的东西

SELECT 
  MainPK
, FName
, LName
, BirthDate
, ForeignDBName
FROM
  MainDB.dbo.MainTable
LEFT JOIN
  (
   SELECT 
     BirthDate
    ,MainTableFK
   FROM
     DB1.dbo.MyTable
   UNION ALL
   SELECT 
     BirthDate
    ,MainTableFK
   FROM
     DB2.dbo.MyTable
   UNION ALL
   SELECT 
     BirthDate
    ,MainTableFK
   FROM
     DB3.dbo.MyTable
   ) B ON B.MainTableFK = MainPK

标签： sqlsql-servertsqlsql-server-2014

解决方案

因为您不知道实际需要多少个数据库，所以您需要运行一些动态 SQL：

DECLARE @statement nvarchar(2048);
DECLARE @db_name varchar(128);
SET @statement='SELECT 
  MainPK
, FName
, LName
, BirthDate
, ForeignDBName
FROM
  MainDB.dbo.MainTable LEFT JOIN ('

DECLARE db_cursor CURSOR FOR 
--adjust database names here
select [name] from sys.databases where [name] in ('DB1','DB2','DB3','DB4','DB5')
ORDER BY name

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @statement=@statement+CHAR(10)+'SELECT BirthDate,MainTableFK FROM '+@db_name+'.dbo.MyTable UNION ALL ';
    FETCH NEXT FROM db_cursor INTO @db_name  
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

-- we remove the last UNION ALL from the statement
SET @statement=LEFT(@statement,LEN(@statement) - 10)

SET @statement=@statement+') B ON B.MainTableFK = MainPK'

-- comment this to hide the statement that will be executed
print @statement;

--uncomment this if you want to actually execute the statement
--exec sp_executesql @statement

推荐阅读