通过本地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