Use local SQL Server to import EXCEL to remote SQL Server

-- Import EXCEL to remote SQL server through local SQL Server

-- 1. Directly import the remote database
Insert
OpenDataSource (
'Sqlodb ',
'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. You can import temporary tables to the local database first and then to the remote database.
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
OpenDataSource (
'Sqlodb ',
'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