分布式查询 opendatasource()

最新推荐文章于 2021-07-31 09:35:39 发布

2303191

最新推荐文章于 2021-07-31 09:35:39 发布

阅读量1.1k

点赞数

文章标签： sql server properties excel insert sqlserver access

本文链接：https://blog.csdn.net/2303191/article/details/1527649

版权

本文介绍了如何使用OpenDataSource在SQL Server中进行异类查询，包括从Access和Excel导入数据到SQL Server，从SQL Server导出数据到Access和Excel，以及在SQL Server中直接查询Excel数据的方法。

摘要由CSDN通过智能技术生成

insert into t_gene_sy_pres (pres_name,address,byrs,edus_id,gdrxnl,kinderclass_id,kindertype_id)

SELECT pres_name,address,cast(byrs as numeric),cast(edus_id as numeric),cast(gdrxnl as numeric),

cast(kinderclass_id as numeric),cast(kindertype_id as numeric) FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source="F:/TEST/TEST.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[T_GENE_SY_PRES]

update opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="F:/各幼儿园师生资料/已处理/zhiye.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] set 职位='其他' where 职位 not in ('园长','教师','保健员','保育员','营养员',保教主任','财务','报帐员','书记','园长助理')

insert into opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="F:/资源.xls";User ID=Admin;Password=;Extended Properties=Excel 5.0')...[Sheet1$]
select p.pres_name,c.cla_name,f.res_title,f.res_path,f.pub_time from opendatasource('SQLOLEDB','Data Source=;User ID=sa;Password=').[pd-baby].dbo.t_gene_re_file f
join opendatasource('SQLOLEDB','Data Source=;User ID=sa;Password=').[pd-baby].dbo.t_gene_re_class c on
f.cla_id = c.cla_id join opendata