透過本地 Excel 以 OPENROWSET 更新遠端 SQL Server 表資料實戰腳本

需求：讀取本地 Excel 檔，更新遠端連結伺服器上資料表特定欄位。完整 T-SQL 腳本如下：

1. 建立連結伺服器

exec sp_addlinkedserver 'web_Server', '', 'sqloledb', 'ServerName'
exec sp_addlinkedsrvlogin 'web_Server', 'false', null, 'sa', 'PassWord'

2. 更新遠端表資料

update srv_lnk.[e-budget].dbo.TA_Current set CYLE3 = b.Y07LE3
from srv_lnk.[e-budget].dbo.TA_Current a
join (
    SELECT f1,
           convert(varchar(50), convert(decimal(18,0), f2)) as account,
           convert(decimal(18,2), f13/1000) as Y07LE3
    FROM OPENROWSET('Microsoft.JET.OLEDB.4.0',
                    'Excel 8.0;HDR=NO;Database=D:\...\420200.xls',
                    'SELECT * FROM [sheet$A18:AC78]'
    ) tableExcel
) b
on a.accountcode = b.account COLLATE Chinese_PRC_CI_AS
where a.costcenter = 420201

關鍵技術點

• OPENROWSET：以 Jet OLEDB Provider 直接查詢 Excel 工作表區域（A18:AC78），無需匯入暫存表。
• 資料型別轉換：Excel 文字欄位轉 decimal 再轉 varchar 對齊遠端 accountcode 型別。
• COLLATE Chinese_PRC_CI_AS：跨伺服器中文比對指定定序，避免定序衝突錯誤。
• HDR=NO：首列非標題，若含標題需設 HDR=YES，否則數值欄位會被誤判為文字導致轉換失敗（作者踩坑：標題列導致數值全變 NULL）。

此腳本示範異構資料源（Excel）透過連結伺服器直接參與遠端資料更新的高效模式，適合定期批次同步場景。