ASP导出MDB为SQL

最初发表于: 2019-03-05 19:17:52

代码如下

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8">
	<title>gosql</title>
</head>
<body>

<% 
'gosql.asp
'运行前修改下面3个数据
database="/_data/db1.mdb"'数据库的相对路径
table="bible"'要导出的表名
sqlFile="bible.sql"'输出的sql文件名

'连接并打开数据库
set conn_sign=server.createobject("adodb.connection") 
conn_sign.open "driver={microsoft access driver (*.mdb)};dbq=" &  server.MapPath(database) 
sql="select * from " & table
set rs=server.createobject("adodb.recordset")  
rs.open sql,conn_sign,1,3  

j=rs.Fields.count '取得该table的字段数量
fieldList=""'将所有字段名称连接成一个字符串,备用
redim fieldListArr(j)'建立关于字段名称的数组

For i=0 to (j-1)   
	fieldList = fieldList + rs.Fields(i).Name + ","
	fieldListArr(i)=rs.Fields(i).Name
Next 
fieldList=left(fieldList,len(fieldList)-1)'去掉最后的逗号

conn_sign.execute sql 

Dim totalnum:totalnum=rs.recordcount '取得数据总条数

html="insert into Songs (" & fieldList & ") values " & vbCrLf'
do while not rs.eof 
	html = html & "('" 
 	For i = 0 to (j - 1) 
 		html=html & rs(fieldListArr(i)) & "','"
 	Next
 	html=left(html,len(html)-2)
 	html=html & ")," & vbCrLf 

    rs.movenext  
loop 

html=left(html,len(html)-3)
html=html&";"

rs.close 
set rs = nothing  
 
'写文件操作
Set fs = Server.CreateObject("Scripting.FileSystemObject")   
Set a = fs.CreateTextFile(server.MapPath(sqlFile), True)   
a.WriteLine(html) 
a.Close 

'生成报告
response.Write "Database: " & database & "<br>"
response.Write "Table: " & table & "<br>" & "<br>"
response.Write "There are " & totalnum & " of data, has wrote to <span style=""color:red"">" & sqlFile & "</span>"
%> 
</body>
</html>

运行以后,将输出一个sql文件