通过asp程序实现批量access数据库导入到sql server数据库中
当前位置:点晴教程→知识管理交流
→『 技术文档交流 』
比如access数据库中有a,b,c三个字段,总共有10条数据,可直接通过asp程序一次性批量导入到sql server数据库中 方法一:先将access数据库中的字段绑定到datagrid再将字段插入sql server数据库 设conn1连接ACCESS,conn2连接SQLServer,在SQLServer数据库中已建好与ACCESS数据库相同表名 set rs=conn1.execute "select * from 表名" do while not(rs.eof or rs.bof) conn2.execute "insert into 表名(a,b,c) values("&rs("a")&","&rs("b")&","&rs("c")) rs.movenext loop 方法二:没办法直接导入,但是可以先建立与access数据库连接 从access表中读出数据,存在一个数据集里,然后再建立与sql数据库的连接,把数据集里的数据一条条插入到sql的目标表中 当然,这个表必须事先建立 要注意的是两个表之间如果字段类型不同,在插入数据时要注意转换类型。 函数
Function ImportData(strTableName,objConnSource,objConnDestination) dim nFieldCount, iLoop dim rsSource dim rsDestination dim strSQL dim strFuncMessage set rsSource = CreateObject("ADODB.RecordSet") set rsDestination = CreateObject("ADODB.RecordSet") strFuncMessage = "" strSQL = "select * from " & strTableName on error resume next rsSource.open strSQL, objConnSource,adOpenStatic,adLockOptimistic if err.number <> 0 then ImportData = -1 exit function end if if objConnSource.Errors.Count > 0 then strFuncMessage = strFuncMessage & "<br>Message: [" & strTableName & "] Not found!" rsSource.Close set rsSource = nothing ImportData = -1 else strMessage = strMessage & "<br>Message:Table[" & strTableName & "] found! " end if nFieldCount = rsSource.Fields.Count redim preserve arrFieldNameList(nFieldCount) For iLoop = 0 to nFieldCount - 1 arrFieldNameList(iLoop) = rsSource.Fields(iLoop).Name Next strSQL = "delete from " & strTableName objConnDestination.Execute(strSQL) strFuncMessage = strFuncMessage & "<br>Message: Delete [" & strTableName & "]" rsDestination.Open strTableName, objConn,adOpenStatic,adLockPessimistic, adCmdTable Do while not rsSource.Eof rsDestination.AddNew For iLoop = 0 to nFieldCount-1 rsDestination(arrFieldNameList(iLoop)) = rsSource(arrFieldNameList(iLoop)) Next RsDestination.Update rsSource.MoveNext Loop rsSource.Close set rsSource = nothing rsDestination.Close strSQL = "select product_id from " & strTableName rsDestination.Open strSQL, objConnSource,adOpenStatic,adLockOptimistic iLoop = rsDestination.RecordCount rsDestination.Close set rsDestination = nothing ImportData = iLoop end function 该文章在 2012/1/31 9:05:21 编辑过 |
关键字查询
相关文章
正在查询... |