[转载]使用asp把数据库中的记录导出到一个新表的方法
文章作者：yezhan信息来源：个人blog（[url]http://www.okhtm.com[/url]）
比如有一个数据表中查询出来的select 的一些数据想导出到另外一个表
用此方法
思路说下吧
就是先建立mdb文件
然后select原来的数据表
然后读出原来的数据 select * from 原来表
然后打开新的另一个表的数据连接
然后使用先前读出来的数据填充数据库 rs.addnew rs.update
最近搞的一个~
嘿嘿~
示例代码如下（思路）
[code] Dim fso,ItemMdb,ItemMdbPath,LeadOutMdb,RsF,SqlF,RsLead,SqlLead,ItemIDTemp
LeadOutMdb=trim(request.form("LeadOutMdb"))
ItemID=trim(request.form("ItemID"))
ItemMdb=DbItem
ItemMdbPath=Left(DbItem,Instrrev(DbItem,"/")-1)
If Instr(ItemMdb,"/")>0 Then
ItemMdbPath=Left(ItemMdb,InstrRev(ItemMdb,"/"))
End If
If LeadOutMdb="" then
FoundErr=True
ErrMsg="<br><li>数据库地址不能为空！</li>"
End If
If ItemID="" Then
FoundErr=True
ErrMsg=ErrMsg & "<br><li>请选择要导出的项目</li>"
Else
ItemID=Replace(ItemID," ","")
End If
If FoundErr<>True And ObjInstalled<>False Then
Set fso = Server.CreateObject("Scripting.FileSystemObject")
If fso.FileExists(Server.MapPath(LeadOutMdb)) Then
Else
'不存在则创建
If fso.FileExists(Server.MapPath(ItemMdbPath & "ItemTemp.mdb")) Then
fso.CopyFile Server.MapPath(ItemMdbPath & "ItemTemp.mdb"),Server.MapPath(LeadOutMdb)
Else
FoundErr=True
ErrMsg=ErrMsg& "<br>用于导出项目的数据库：ItemTemp.mdb不存在！"
End If
End If
set fso=nothing
End If
If FoundErr<>True Then
dim connstrLead,connLead
Set connLead = Server.CreateObject("ADODB.Connection")
connstrLead="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(LeadOutMdb)
connLead.Open connstrLead
If Err Then
err.Clear
FoundErr=True
ErrMsg=ErrMsg & "<br>数据库连接出错，请确认数据库是否存在。"
End If
If FoundErr<>True Then
ConnLead.execute("Delete From Item")
ConnLead.execute("Delete From Filters")
Set RsItem=server.createobject("adodb.recordset")
SqlItem="select * from Item where ItemID in(" & ItemID & ") order by ItemID DESC"
RsItem.open SqlItem,ConnItem,1,1
If Not RsItem.Eof then
Do while Not RsItem.Eof
'打开数据库
Set RsLead=server.createobject("adodb.recordset")
SqlLead="select * from Item"
RsLead.open SqlLead,ConnLead,1,3
RsLead.AddNew
RsLead("ItemName")=RsItem("ItemName")
RsLead("ChannelID")=RsItem("ChannelID")
RsLead("ChannelDir")=RsItem("ChannelDir")
RsLead("ClassID")=RsItem("ClassID")
RsLead("SpecialID")=RsItem("SpecialID")
RsLead("WebName")=RsItem("WebName")
RsLead("WebUrl")=RsItem("WebUrl")
RsLead("ItemDemo")=RsItem("ItemDemo")
RsLead("LoginType")=RsItem("LoginType")
RsLead("LoginUrl")=RsItem("LoginUrl")
RsLead("LoginPostUrl")=RsItem("LoginPostUrl")
RsLead("LoginUser")=RsItem("LoginUser")
RsLead("LoginPass")=RsItem("LoginPass")
RsLead("LoginFalse")=RsItem("LoginFalse")
RsLead("ListStr")=RsItem("ListStr")
RsLead("LsString")=RsItem("LsString")
RsLead("LoString")=RsItem("LoString")
RsLead("ListPaingType")=RsItem("ListPaingType")
RsLead("LPsString")=RsItem("LPsString")
RsLead("LPoString")=RsItem("LPoString")
RsLead("ListPaingStr1")=RsItem("ListPaingStr1")
RsLead("ListPaingStr2")=RsItem("ListPaingStr2")
RsLead("ListPaingID1")=RsItem("ListPaingID1")
RsLead("ListPaingID2")=RsItem("ListPaingID2")
RsLead("ListPaingStr3")=RsItem("ListPaingStr3")
RsLead("HsString")=RsItem("HsString")
RsLead("HoString")=RsItem("HoString")
RsLead("HttpUrlType")=RsItem("HttpUrlType")
RsLead("HttpUrlStr")=RsItem("HttpUrlStr")
RsLead("TsString")=RsItem("TsString")
RsLead("ToString")=RsItem("ToString")
RsLead("CsString")=RsItem("CsString")
RsLead("CoString")=RsItem("CoString")
RsLead("DateType")=RsItem("DateType")
RsLead("DsString")=RsItem("DsString")
RsLead("DoString")=RsItem("DoString")
RsLead("AuthorType")=RsItem("AuthorType")
RsLead("AsString")=RsItem("AsString")
RsLead("AoString")=RsItem("AoString")
RsLead("AuthorStr")=RsItem("AuthorStr")
RsLead("CopyFromType")=RsItem("CopyFromType")
RsLead("FsString")=RsItem("FsString")
RsLead("FoString")=RsItem("FoString")
RsLead("CopyFromStr")=RsItem("CopyFromStr")
RsLead("KeyType")=RsItem("KeyType")
RsLead("KsString")=RsItem("KsString")
RsLead("KoString")=RsItem("KoString")
RsLead("KeyStr")=RsItem("KeyStr")
RsLead("NewsPaingType")=RsItem("NewsPaingType")
RsLead("NPsString")=RsItem("NPsString")
RsLead("NPoString")=RsItem("NPoString")
RsLead("NewsPaingStr")=RsItem("NewsPaingStr")
RsLead("NewsPaingHtml")=RsItem("NewsPaingHtml")
RsLead("PaginationType")=RsItem("PaginationType")
RsLead("MaxCharPerPage")=RsItem("MaxCharPerPage")
RsLead("ReadLevel")=RsItem("ReadLevel")
RsLead("Stars")=RsItem("Stars")
RsLead("ReadPoint")=RsItem("ReadPoint")
RsLead("Hits")=RsItem("Hits")
RsLead("UpDateType")=RsItem("UpDateType")
RsLead("UpDateTime")=RsItem("UpDateTime")
RsLead("IncludePicYn")=RsItem("IncludePicYn")
RsLead("DefaultPicYn")=RsItem("DefaultPicYn")
RsLead("OnTop")=RsItem("OnTop")
RsLead("Elite")=RsItem("Elite")
RsLead("Hot")=RsItem("Hot")
RsLead("SkinID")=RsItem("SkinID")
RsLead("TemplateID")=RsItem("TemplateID")
RsLead("Script_Iframe")=RsItem("Script_Iframe")
RsLead("Script_Object")=RsItem("Script_Object")
RsLead("Script_Script")=RsItem("Script_Script")
RsLead("Script_Div")=RsItem("Script_Div")
RsLead("Script_Class")=RsItem("Script_Class")
RsLead("Script_Span")=RsItem("Script_Span")
RsLead("Script_Img")=RsItem("Script_Img")
RsLead("Script_Font")=RsItem("Script_Font")
RsLead("Script_A")=RsItem("Script_A")
RsLead("Script_Html")=RsItem("Script_Html")
RsLead("CollecListNum")=RsItem("CollecListNum")
RsLead("CollecNewsNum")=RsItem("CollecNewsNum")
RsLead("Passed")=RsItem("Passed")
RsLead("SaveFiles")=RsItem("SaveFiles")
RsLead("CollecOrder")=RsItem("CollecOrder")
RsLead("LinkUrlYn")=RsItem("LinkUrlYn")
RsLead("InputerType")=RsItem("InputerType")
RsLead("Inputer")=RsItem("Inputer")
RsLead("EditorType")=RsItem("EditorType")
RsLead("Editor")=RsItem("Editor")
RsLead("ShowCommentLink")=RsItem("ShowCommentLink")
RsLead("Script_Table")=RsItem("Script_Table")
RsLead("Script_Tr")=RsItem("Script_Tr")
RsLead("Script_Td")=RsItem("Script_Td")
RsLead("Flag")=RsItem("Flag")
ItemIDTemp=RsLead("ItemID")
RsLead.Update
RsLead.Close
Set RsLead=Nothing
'过滤信息
Set RsF=server.createobject("adodb.recordset")
SqlF="select * from Filters Where ItemID=" & RsItem("ItemID") & " order by ItemID DESC"
RsF.open SqlF,ConnItem,1,1
If Not RsF.Eof then
Do While Not RsF.Eof
Set RsLead=server.createobject("adodb.recordset")
SqlLead="select * from Filters"
RsLead.open SqlLead,ConnLead,1,3
RsLead.AddNew
RsLead("ItemID")=ItemIDTemp
RsLead("FilterName")=RsF("FilterName")
RsLead("FilterObject")=RsF("FilterObject")
RsLead("FilterType")=RsF("FilterType")
RsLead("FilterContent")=RsF("FilterContent")
RsLead("FisString")=RsF("FisString")
RsLead("FioString")=RsF("FioString")
RsLead("FilterRep")=RsF("FilterRep")
RsLead("Flag")=RsF("Flag")
RsLead("PublicTf")=RsF("PublicTf")
RsLead.Update
RsLead.Close
Set RsLead=Nothing
RsF.MoveNext
Loop
End If
RsF.Close
Set RsF=Nothing
RsItem.MoveNext
Loop
End If
RsItem.Close
Set RsItem=Nothing
End If
ConnLead.close
set connlead=nothing
End If
If FoundErr<>True Then
ErrMsg="<br>数据导出成功"
ErrMsg=ErrMsg & "<br>数据导出为：" & LeadOutMdb
Call WriteSucced(ErrMsg)
Else
Call WriteErrMsg(ErrMsg)
End If
[/code]
[s:35] [s:35] [s:46] 来10万条记录,你试试
居然是一次拷完的!??
别处的不说,少了timeout吧 是同一个数据库中的两表互导吗?代码太长没去看,感觉这也太烦琐了.
同一个数据库两表互导最佳办法:
conn.execute("insert into table1(field1,field2,....) select field1,field2,.... from table2")
页:
[1]