SqlServer通过链接服务器跨库查询多个数据库合并结果集

Test2 varchar(100),

Test3 varchar(100)

)

–定义数据库连接

declare @sDblink table

(

sDbIP varchar(100),–数据库IP

sDbName varchar(100),–数据库名称

sDbDisplayName varchar(100)–数据库别名

)

–查询数据库连接

insert into @sDblink

select t.sServerName as sDbIP,t.sDbName,t.sCompanyName as sDbDisplayName

from smlocalcompany t

LEFT JOIN dbo.smLocalCompany_Order T0 ON t.iCompanyID=T0.iCompanyID

where isnull(t.sServerName,‘’)<>‘’ AND t.bUsable=1

–定义游标

declare cursor_test cursor for

select * from @sDblink

open cursor_test

fetch next from cursor_test into 《大厂前端面试题解析+Web核心总结学习笔记+企业项目实战源码+最新高清讲解视频》无偿开源 徽信搜索公众号【编程进阶路】 @sDbIP,@sDbName,@sDbDisplayName

–循环

while @@FETCH_STATUS = 0 begin

set @sqlstr = ’

select ‘’‘+@sDbDisplayName+’‘’ as Test1,A.Name as Test2,A.Groups as Test3

FROM [‘+@sDbIP+’].[‘+@sDbName+’].[dbo].PM_ColorSystemGroup A

’

INSERT INTO @Test1

exec (‘’+@sqlstr+‘’)

fetch next from cursor_test into @sDbIP,@sDbName,@sDbDisplayName

end