我在SSMS中运行下面的查询,您可以看到它显示了来自不同数据库的多个表的结果。
如何将这些表合并为一个表?
我尝试添加UNION和UNION ALL到SELECT @Sql = COALESCE(@Sql + ' UNION ' + CHAR(13) + CHAR(10), '' ) +
,但我得到Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.
错误
任何想法都将非常感激!
DECLARE @table table(dbname sysname COLLATE Latin1_General_CI_AS)
INSERT INTO @table(dbname)
SELECT NAME COLLATE Latin1_General_CI_AS FROM sys.databases where name like '%AccountsLive'
DECLARE @Sql NVARCHAR(MAX) = NULL;
SELECT @Sql = COALESCE(@Sql + CHAR(13) + CHAR(10), '' ) +
'SELECT c_broker, det_costheader, cuname, ch_name, CONVERT(VARCHAR(11),ch_date_req,103) AS ''Flight_Date'', IIF(OUT.outstanding>0, ''Yes'', ''No'') AS ''Anything_Outstanding?'', sum(c_grossmargin)/2 AS ''Gross_Margin'' FROM '
+ QUOTENAME(dbname) COLLATE Latin1_General_CI_AS + '.dbo.AT_ACS_COMMISSIONS AS COM
LEFT JOIN ' + QUOTENAME(dbname) COLLATE Latin1_General_CI_AS + '.dbo.AT_ACS_PROJECTOUTSTANDING AS OUT
ON COM.det_costheader = OUT.project
WHERE c_broker = ''HKGCGOJT''
GROUP BY det_costheader, c_broker, cuname, ch_name, ch_date_req, outstanding'
FROM @table
exec( @Sql );
如果任何列在不同的数据库中有不同的排序,那么您需要向它们添加COLLATE
。我已经添加了一些向您展示如何完成。
指出:
- 这里不需要table变量
- 使用
STRING_AGG
(或FOR XML
)聚合,不要使用可变聚并,不安全。 - 如果您打算添加参数,则使用
sp_executesql
并将参数传递给动态部分。将参数连接到查询中。
DECLARE @Sql NVARCHAR(MAX);
SET @Sql =
(SELECT
STRING_AGG(
N'SELECT
c_broker COLLATE Latin1_General_CI_AS,
det_costheader,
cuname COLLATE Latin1_General_CI_AS,
ch_name,
CONVERT(VARCHAR(11), ch_date_req, 103) AS [Flight_Date],
IIF(OUT.outstanding > 0, ''Yes'', ''No'') AS [Anything_Outstanding?],
sum(c_grossmargin) / 2 AS [Gross_Margin]
FROM ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_COMMISSIONS AS COM
LEFT JOIN ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_PROJECTOUTSTANDING AS OUT
ON COM.det_costheader = OUT.project
WHERE c_broker = ''HKGCGOJT''
GROUP BY det_costheader, c_broker, cuname, ch_name, ch_date_req, outstanding
',
CAST (N'
UNION ALL
' AS nvarchar(max)) )
FROM sys.databases d
where name like '%AccountsLive'
);
exec( @Sql );
如果你使用的是没有STRING_AGG
的旧版本的SQL Server,那么你需要使用FOR XML
来聚合:
DECLARE @Sql NVARCHAR(MAX);
SET @Sql =
STUFF(
(SELECT
NCHAR(10) + N'UNION ALL' + NCHAR(10) +
N'SELECT
c_broker COLLATE Latin1_General_CI_AS,
det_costheader,
cuname COLLATE Latin1_General_CI_AS,
ch_name,
CONVERT(VARCHAR(11), ch_date_req, 103) AS [Flight_Date],
IIF(OUT.outstanding > 0, ''Yes'', ''No'') AS [Anything_Outstanding?],
sum(c_grossmargin) / 2 AS [Gross_Margin]
FROM ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_COMMISSIONS AS COM
LEFT JOIN ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_PROJECTOUTSTANDING AS OUT
ON COM.det_costheader = OUT.project
WHERE c_broker = ''HKGCGOJT''
GROUP BY det_costheader, c_broker, cuname, ch_name, ch_date_req, outstanding
'
FROM sys.databases d
where name like '%AccountsLive'
FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'), 1, 11, '');
exec( @Sql );
我建议使用PRINT @Sql
用于测试目的,以确保生成的SQL实际上是有效的。