SQL-从多个数据库中选择

Question

我有10个数据库，它们都在同一个实例和模式中，具有相同的表。

希望创建一个SELECT查询，该查询可以使用数据库名称的变量，并将所有记录返回到单个数据集中。

我一直在四处寻找，并将以下内容放在一起作为测试(真正的select语句在多个联接中要大得多(

这个测试确实有效，但它返回两个结果集，每个数据库一个。

有没有办法把结果组合成一组，或者我用错了方法？

提前感谢

DECLARE @DB_NAME VARCHAR(6);
DECLARE CURSOR_ALLDB_NAMES CURSOR FOR 
SELECT name
FROM sys.databases
WHERE name IN ('CN2DAT', 'AU1DAT') 
OPEN CURSOR_ALLDB_NAMES
FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
WHILE @@Fetch_Status = 0
BEGIN
EXEC ('SELECT * FROM ' + @DB_NAME + '.dbo.ICITEM')
-- EXEC ('USE '+ @DB_NAME + ' SELECT * from dbo.ICITEM')
FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
END
CLOSE CURSOR_ALLDB_NAMES
DEALLOCATE CURSOR_ALLDB_NAMES

Answer 1

我会创建一个视图来组合select语句。例如

CREATE VIEW v_ICITEM
AS
SELECT * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT * FROM AU1DAT.dbo.ICITEM
go;

您也可以将源数据库作为列包括在内：

CREATE VIEW v_ICITEM
AS
SELECT 'CN2DAT' AS Db, * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT 'AU1DAT', * FROM AU1DAT.dbo.ICITEM
go;

Answer 2

将结果存储到临时表中，例如：

CREATE TABLE #TempICITEM 
([DbName] [varchar](6) NOT NULL,
[ICITEMId] [uniqueidentifier] NOT NULL,
[Name] [varchar](100) NOT NULL,
[Description] [varchar](1000) NOT NULL)

将您的TSQL选择更改为插入#TempTable选择，例如：

EXEC ('INSERT INTO #TempICITEM ([DbName],[ICITEMId],[Name],[Description]) SELECT ''' + @DB_NAME + ''' DbName,[DbName],[ICITEMId],[Name],[Description] FROM ' + @DB_NAME + '.dbo.ICITEM')

然后从临时表的组合结果中选择，例如：

SELECT * From #TempICITEM

加在一起：

CREATE TABLE #TempICITEM 
([DbName] [varchar](6) NOT NULL,
[ICITEMId] [uniqueidentifier] NOT NULL,
[Name] [varchar](100) NOT NULL,
[Description] [varchar](1000) NOT NULL)
DECLARE @DB_NAME VARCHAR(6);
DECLARE CURSOR_ALLDB_NAMES CURSOR FOR 
SELECT name
FROM sys.databases
WHERE name IN ('CN2DAT', 'AU1DAT') 
OPEN CURSOR_ALLDB_NAMES
FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
WHILE @@Fetch_Status = 0
BEGIN
EXEC ('INSERT INTO #TempICITEM ([DbName],[ICITEMId],[Name],[Description]) SELECT ''' + @DB_NAME + ''' DbName,[DbName],[ICITEMId],[Name],[Description] FROM ' + @DB_NAME + '.dbo.ICITEM')
FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
END
CLOSE CURSOR_ALLDB_NAMES
DEALLOCATE CURSOR_ALLDB_NAMES
SELECT * From #TempICITEM