環境說明：
在一些IOT情境，設備的資料拆散到不同的DB內，而每個DB的資料結構都一樣。
每個DB存的是各自機器的資料，而我想要做的動作是，
查出散布在不同DB內，最新的資料。
以下是循序的思路，想要結果可以拉到文章末看語法。
1.查詢語法中，可用變數指定不同的DB，再執行
|
declare @dbName nvarchar(50), EXEC (@query) |
可以查出資料內容
但是要如何把所有的機具名稱拿到？
2.取得所有DB名稱
| SELECT ROW_NUMBER() OVER(ORDER BY name) AS ID, name FROM master.sys.databases where name like 'EquAnal_%' |
裡面用了 ROW_NUMBER() OVER(ORDER BY name) 的語法，
將資料排序並編號，方便識別。
取出來所有DB的名稱
接下來的問題是，怎麼把這麼多的DB名稱放進查詢裡，用迴圈來跑
3.使用Table變數暫存資料
|
declare @equAnalDbName table insert into @equAnalDbName (ID, name)
|
這邊使用Table型態的 @變數，可以在指令完成後自動刪除，
不用考慮是否要drop table。
使用insert into 語法，將查出來的資料直接放入Table變數。
最後再查出來檢查一下。
4.結合步驟1中的動態DB來源查詢語法，放入迴圈中
將查詢語法置於迴圈中，可分批查詢出想要的資料。
不過如此一來，所有資料都散在不同資料框中，無法進行排序，取得最新的。
為了將所有資料合在一起，使用了 union 指令，
在迴圈中僅組合語法，迴圈外再進行一次查詢。
完整語法
|
declare @equAnalDbName table ( ID INT , --ROW序號 name NVARCHAR(100) )
insert into @equAnalDbName (ID, name) (SELECT ROW_NUMBER() OVER(ORDER BY name) AS ID, name FROM master.sys.databases where name like 'EquAnal_%')
DECLARE @TabelCount INT --loop的條件 ,@WhileTableCount INT = 1 -- ,@dbNum INT ,@dbName VARCHAR(100) ,@query nvarchar(500) ,@unionQuery nvarchar(4000)
SET @unionQuery = ''; SET @TabelCount = (SELECT COUNT(ID) FROM @equAnalDbName);
--以下開始loop WHILE @WhileTableCount <= @TabelCount BEGIN
--透過@WhileTableCount的定位取得每個row的值 SELECT @dbNum = ID, @dbName = name from @equAnalDbName where ID = @WhileTableCount
----Do Something Start---- if(@WhileTableCount > 1) begin set @unionQuery += ' union '; end
set @query = ' (select *, '''+@dbName+''' as dbName from ['+@dbName+'].[dbo].[consecMins] ) '; set @unionQuery += @query; ----Do Something End----
--迴圈遞增 SET @WhileTableCount = @WhileTableCount + 1 END
set @unionQuery = @unionQuery + ' order by endDateMin desc';
EXEC (@unionQuery); |
查詢結果
順利的將不同DB內的資料查詢出來，放在一起排序，取得最新的。
如果本篇文章有幫助到你，請給我一個讚，
你的回饋會讓技術分享生態更美好。
參考資料
參考整體流程
https://dotblogs.com.tw/mola/2012/04/25/71786
參考暫存table觀念
https://ithelp.ithome.com.tw/articles/10225120
參考查詢變數內資料表
https://stackoverflow.com/questions/2838490/table-name-as-variable
留言列表
程式筆記 (1)