通過本地sql server 將excel匯入遠程sql server

--通過本地sql  server 將excel匯入遠程sql  server

--1、直接匯入遠端資料庫
insert into
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=SHIPMISSERVER/SQLSERVER2005;User ID=sa;Password=sa123$'
         ).LSShipMis_4500.dbo.T_WORKINFO_TEMPLATE
(WORKINFO_TEMPLATE_ID,COMPONENTREFERENCE,ITEMNAME,ITEMCONTENT,PERIOD,REMARK)
select newid(),* FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:/LsShipmis/工作資訊整理/工作資訊整理.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=no" ')...[Sheet2$]

 

--2、可以先匯入本機資料庫暫存資料表、再匯入遠端資料庫
SELECT * into #tt
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:/LsShipmis/工作資訊整理/工作資訊整理.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=no" ')...[Sheet2$]

insert into
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=SHIPMISSERVER/SQLSERVER2005;User ID=sa;Password=sa123$'
         ).LSShipMis_4500.dbo.T_WORKINFO_TEMPLATE
(WORKINFO_TEMPLATE_ID,COMPONENTREFERENCE,ITEMNAME,ITEMCONTENT,PERIOD,REMARK)
select newid(),* from #tt