EXCEL数据批量导入SQLSERVER2008备忘记录

一、开启批量 导入

--启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 

 

 

二、OFFICE2003版本用MICROSOFT.JET.OLEDB.4.0

OFFICE2007及以后版本用Microsoft.ACE.OLEDB.12.0

如：

set @tableName='各题得分'+@njhao
	--更新已存在的学生成绩	
	set @sql='update '+@tableName+' set '+@zdStr1+' from '+@tableName+' a
	inner join (
	SELECT 学生ID,'+@zdStr0+'
	 FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;IMEX=1;HDR=YES;DATABASE='+@drFileName+''',[getidefen$])
	) b on a.学生ID=b.学生ID where 学科='''+@xueke+''' and 考试ID='+@ksId
	exec(@sql)

 

 

三、在ASP中批量导入的自定义函数（通过hbh类调用数据库的conn）

Function excelToDb(xlsFile, tableName, fieldStr, del)
    excelToDb = 0
    Dim xlsFilePath, fso
    xlsFilePath = Server.MapPath("data\Tmp" & xlsFile & ".xls")
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(xlsFilePath) Then
        Set fso = Nothing
        excelToDb = "对不起，Tmp" & xlsFile & ".xls文件不存在！"
        Exit Function
    End If
    Set fso = Nothing
    If del = 1 Then hbh.conn.Execute ("delete from " & tableName & " where ID>1")
    Dim n1, n2
    n1 = hbh.shsl("select count(*) from " & tableName)
    sql = "INSERT INTO " & tableName & "(" & fieldStr & ") SELECT " & fieldStr & " FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE="
    sql = sql & xlsFilePath & "',[" & xlsFile & "$])"
    hbh.conn.Execute (sql)
    n2 = hbh.shsl("select count(*) from " & tableName)
    excelToDb = n2 - n1
End Function