代码如下
<!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文件