麻烦问下sql多数据库多表查询的问题

发布网友发布时间：2022-04-10 00:09

共3个回答

热心网友时间：2022-04-10 01:39

给个笨办法把挨着写要不就写个存储过程来生成这个sql语句

select * from database01.biao01 where name = 123

union all
select * from database01.biao02 where name = 123
union all
select * from database01.biao03 where name = 123
..............................
union all
select * from database07.biao399 where name = 123
union all
select * from database08.biao400 where name = 123
谁有简单的方法么

热心网友时间：2022-04-10 02:57

USE master

IF object_id(N'tempdb..##tempsalary')<>0
BEGIN
DROP TABLE ##tempsalary
END
CREATE TABLE ##tempsalary
(
id int,
name nvarchar(100),
age decimal(4,2)
)

DECLARE @tablename varchar(50)
DECLARE @dataname varchar(50)
DECLARE @sql varchar(1000)
DECLARE @t int
DECLARE @d int
SET @t =1
SET @d =1

WHILE (@d <=8)
BEGIN
SET @dataname='数据库前缀'+convert(varchar(1),@d)
WHILE (@t<=@d*50 AND @t>(@d-1)*50)
BEGIN
SET @tablename='dbo.EmployeeSalary'+(CASE WHEN len(@t)=1 THEN '0'+CONVERT(varchar(2),@t) ELSE CONVERT(varchar(3),@t)end)
SET @sql='insert into ##tempsalary(id,name,age)
select id,name,age from @dataname.dbo.@tablename where name=''123'''
EXEC @sql
set @t=@t+1
end
SET @d=@d+1
END

SELECT * FROM ##tempsalary
DROP TABLE ##tempsalary

热心网友时间：2022-04-10 04:31

这是个问题