sql server游标读取excel文件数据，更新到指定表中
时间：2021-07-01 10:21:17
帮助过：16人阅读
declare @a int,
@error int
DECLARE @EBSCode nvarchar(
50)
DECLARE @EBSName nvarchar(
50)
DECLARE @FilePath nvarchar(
200)
set @a=1
set @error=0
DECLARE table_cursor
CURSOR FOR
(select * from OpenDataSource (
‘Microsoft.Ace.OLEDB.12.0‘,
‘Data Source="D:\20191217\123.xlsx";Extended properties="Excel 12.0;HDR=Yes"‘)...
[sheet1$])
OPEN table_cursor
FETCH NEXT FROM table_cursor
into @EBSCode,
@EBSName,
@FilePath
WHILE @@FETCH_STATUS=0
BEGIN
update Basic_ModelImport
set FilePath
=‘/jfiles/upload/20191217/‘+@FilePath where IsDeleted
=0 and [Type]=0 and TenantId
=1 and EBSCode
=@EBSCode
set @a=@a+1
set @error= @error + @@ERROR --记录每次运行sql后是否正确，0正确
FETCH NEXT FROM table_cursor
into @EBSCode,
@EBSName,
@FilePath
END
CLOSE table_cursor
DEALLOCATE table_cursor
end
go
select * from OpenDataSource (
‘Microsoft.Ace.OLEDB.12.0‘,
‘Data Source="D:\20191217\123.xlsx";Extended properties="Excel 12.0;HDR=Yes"‘)...
[sheet1$]
--开启
exec sp_configure
‘show advanced options‘,
1
reconfigure
exec sp_configure
‘Ad Hoc Distributed Queries‘,
1
reconfigure
--关闭
exec sp_configure
‘Ad Hoc Distributed Queries‘,
0
reconfigure
exec sp_configure
‘show advanced options‘,
0
reconfigure
--允许在进程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N
‘Microsoft.ACE.OLEDB.12.0‘, N
‘AllowInProcess‘,
1
--允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N
‘Microsoft.ACE.OLEDB.12.0‘, N
‘DynamicParameters‘,
1
--不允许在进程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N
‘Microsoft.ACE.OLEDB.12.0‘, N
‘AllowInProcess‘,
0
--不允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N
‘Microsoft.ACE.OLEDB.12.0‘, N
‘DynamicParameters‘,
0
sql server游标读取excel文件数据，更新到指定表中
标签：file for ast upload upd microsoft pat server ESS
