SqlServer 怎样实现动态视图查询多个库的数据

[游泳的猪]

在[A]库中有一张表结构如下
COMPANY
CITY_CODE DATABASE_NM
---------------------------------------------
CNY DATABASE01
SHA DATABASE02
USD DATABASE03
MFM DATABASE04

具体需求是：从A库的[COMPANY]中得到每一个货币对应其他国家货币的汇率，[COMPANY]的CITY_CODE的值代表本地货币种类，[DATABASE_NM]的值是关联的数据库的名称。库[DATABASE01]~[DATABASE02]就是我们要得到的本地货币对外币的汇率表。

之前写的视图是：
SELECT

查询SQL语句
FROM
[DATABASE01].dbo.XX A
LEFT JOIN [DATABASE01].dbo.XXX B ON A.cexch_name = B.cexch_name AND B.i_id <> '1'
LEFT JOIN [DATABASE01].dbo.XXXX C ON C.i_id = '1'

WHERE
A.itype = '2'

UNION

SELECT

查询SQL语句
FROM
[DATABASE02].dbo.XX A
LEFT JOIN [DATABASE02].dbo.XXX B ON A.cexch_name = B.cexch_name AND B.i_id <> '1'
LEFT JOIN [DATABASE02].dbo.XXXX C ON C.i_id = '1'

WHERE
A.itype = '2'

UNION

SELECT

查询SQL语句
FROM
[DATABASE03].dbo.XX A
LEFT JOIN [DATABASE03].dbo.XXX B ON A.cexch_name = B.cexch_name AND B.i_id <> '1'
LEFT JOIN [DATABASE03].dbo.XXXX C ON C.i_id = '1'

WHERE
A.itype = '2'

UNION

SELECT

查询SQL语句
FROM
[DATABASE04].dbo.XX A
LEFT JOIN [DATABASE04].dbo.XXX B ON A.cexch_name = B.cexch_name AND B.i_id <> '1'
LEFT JOIN [DATABASE04].dbo.XXXX C ON C.i_id = '1'

WHERE
A.itype = '2'


现在我们项目要求，写成一个循环语句 因为[A]的[Company]表的数据不是固定的，有可能会追加其他国家的货币种类！
求大神指导啊，已经卡在这里一个多星期了。
我的想法是：假设有20个国家使用这个网站，然后写一个存储过程（pro_test），循环A库，表[COMPANY]的所有数据：
IF (@dateBase_nm = 'DATABASE01')
BEGIN

INSERT INTO A.dbo.EXCHANGE_RATE_INFO
(A, B, C)
SELECT A,B,C
FROM
[DATABASE01].dbo.Department
END

IF (@dateBase_nm = 'DATABASE02')
BEGIN

INSERT INTO A.dbo.EXCHANGE_RATE_INFO
(A, B, C)
SELECT A,B,C
FROM
[DATABASE02].dbo.Department
END
........
[EXCHANGE_RATE_INFO]是一个临时表，插入完数据，用视图(view_test)查询[EXCHANGE_RATE_INFO]，以便在项目中使用，问题在于 我怎么使用视图先调用一个存储过程[pro_test]，再查询[EXCHANGE_RATE_INFO]？或者大家有什么更简单的办法来实现这样的功能。（散100分 大神们还等什么？）

[yuehua0920]

同求大神指教！！！

[gaijiuyang]

顶起来，等结果

[lanseyoumeng132]

语法 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } } 试试这样 可以吗?

[游泳的猪]

引用 4 楼 lanseyoumeng132 的回复:

语法 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } } 试试这样 可以吗?

是在视图里写触发器对吧。我试试！

[gaijiuyang]

触发器，当主数据库添加一条记录时，临时表EXCHANGE_RATE_INFO就添加一条记录，视图直接查询临时表EXCHANGE_RATE_INFO取得结果。 CREATE TRIGGER [COMPANY_EXCHANGE_INFO] ON A.dbo.COMPANY FOR INSERT AS insert into A.dbo.EXCHANGE_RATE_INFO(A, B, C) select d.A,d.B,d.C from Inserted.DATABASE_NM.dbo.XXXX d where d.i_id = '1' 猜的，试试可以吗

[游泳的猪]

引用 6 楼 gaijiuyang 的回复:

触发器，当主数据库添加一条记录时，临时表EXCHANGE_RATE_INFO就添加一条记录，视图直接查询临时表EXCHANGE_RATE_INFO取得结果。 CREATE TRIGGER [COMPANY_EXCHANGE_INFO] ON A.dbo.COMPANY FOR INSERT AS insert into A.dbo.EXCHANGE_RATE_INFO(A, B, C) select d.A,d.B,d.C from Inserted.DATABASE_NM.dbo.XXXX d where d.i_id = '1' 猜的，试试可以吗

我的触发器： CREATE TRIGGER trig_exchange_rate ON MST_COMPANY_INFO FOR INSERT,UPDATE,DELETE AS BEGIN EXEC pro_name END; 这样调用存储过程是可以的，但是pro_name出错了 DB就挂了，没办法rollback啊。

[gaijiuyang]

引用 7 楼 libowen_beijing 的回复:

[quote=引用 6 楼 gaijiuyang 的回复:] 触发器，当主数据库添加一条记录时，临时表EXCHANGE_RATE_INFO就添加一条记录，视图直接查询临时表EXCHANGE_RATE_INFO取得结果。 CREATE TRIGGER [COMPANY_EXCHANGE_INFO] ON A.dbo.COMPANY FOR INSERT AS insert into A.dbo.EXCHANGE_RATE_INFO(A, B, C) select d.A,d.B,d.C from Inserted.DATABASE_NM.dbo.XXXX d where d.i_id = '1' 猜的，试试可以吗

我的触发器： CREATE TRIGGER trig_exchange_rate ON MST_COMPANY_INFO FOR INSERT,UPDATE,DELETE AS BEGIN EXEC pro_name END; 这样调用存储过程是可以的，但是pro_name出错了 DB就挂了，没办法rollback啊。[/quote] 不用存储过程了，直接写sql，向中间表添加数据，一个sql不能写错吧

[游泳的猪]

不能沉啊！

[游泳的猪]

哦了，问题已经解决。感谢gaijiuyang 和 lanseyoumeng132 我的解决思路是 写了个触发器： CREATE TRIGGER [dbo].[NewTrigger] ON [dbo].[MST_COMPANY_INFO] AFTER INSERT, UPDATE, DELETE AS BEGIN declare @a int, @database_nm varchar(100) set @a = 1 -- 把中间表的数据全部删除 DELETE FROM MST_EXCHANGE_RATE_INFO; -- 查询a库的主表 循环 WHILE @a < (SELECT COUNT(1) FROM MST_COMPANY_INFO WHERE MST_COMPANY_INFO.DATEBASE_NAME <> '') BEGIN -- 获取主表当前循环的库名 SET @database_nm = (SELECT TOP(1) A.DATEBASE_NAME FROM MST_COMPANY_INFO A WHERE A.DATEBASE_NAME <> '' AND A.OFFICE_CODE IN (SELECT TOP(@a) B.OFFICE_CODE FROM MST_COMPANY_INFO B WHERE B.DATEBASE_NAME <> '' ORDER BY B.OFFICE_CODE ASC) ORDER BY A.OFFICE_CODE DESC); -- 循环的参数加1 SET @a = @a + 1; -- 判断@database_nm是哪个库名 然后插入数据 IF @database_nm = 'DATABASE1' BEGIN INSERT INTO MST_EXCHANGE_RATE_INFO ( ORIGINAL_CURRENCY_CODE, TARGET_CURRENCY_CODE, EXCHANGE_RATE, APPLY_START_DATE, APPLY_END_DATE ) SELECT CASE B.cexch_code WHEN 'RMB' THEN 'CNY' ELSE B.cexch_code END AS ORIGINAL_CURRENCY_CODE, CASE C.cexch_code WHEN 'RMB' THEN 'CNY' ELSE C.cexch_code END AS TARGET_CURRENCY_CODE, CASE B.bcal WHEN 1 THEN CAST(A.nflat AS DECIMAL(15,7)) ELSE CAST (1 / A.nflat AS DECIMAL(15,7)) END AS EXCHANGE_RATE, CAST(A.iYear + right('00'+A.iperiod, 2) + '01' as date) AS APPLY_START_DATE, CAST(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,cast(A.iYear + right('00'+A.iperiod, 2) + '01' as date))+1, 0)) AS DATE) AS APPLY_END_DATE FROM [DATABASE1].dbo.exch A LEFT JOIN [DATABASE1].dbo.foreigncurrency B ON A.cexch_name = B.cexch_name AND B.iotherused <> -1 LEFT JOIN [DATABASE1].dbo.foreigncurrency C ON C.iotherused = -1 WHERE A.itype = '2' END .............. END END 再写一个视图： 视图里只需要查询[MST_EXCHANGE_RATE_INFO]虚拟表： SELECT DISTINCT * FROM MST_EXCHANGE_RATE_INFO 就可以了。 不多说 散分。