测试为:sheet2$,sheet3$两张表都有name字段,根据name字段值相等,将sheet3$中的coltest列添加到sheet2$中的coltest列,测试语句如下:
Declare @coltest nvarchar(255)
Declare @name nvarchar(255)
Declare MyCursor Cursor Scroll
For select coltest,name from openrowset('microsoft.jet.oledb.4.0','excel 5.0;hdr=yes;database=D:/My Documents/test.xls;',sheet3$)
open MyCursor
Fetch next From MyCursor
Into @coltest,@name
While(@@Fetch_Status = 0)
Begin
Begin
update openrowset('microsoft.jet.oledb.4.0','excel 5.0;hdr=yes;database=D:/My Documents/test.xls;',sheet2$)
set coltest=@coltest
where name=@name
End
Fetch next From MyCursor
Into @coltest,@name
End
Close MyCursor
Deallocate MyCursor