两个数据库之间的Insert into select 问题,请高手帮忙看看。。
打开了两个数据连接,一个是外部文件连接的 cn_db ,一个是SQL的数据连接 cn_sql
‘总库’是SQL中的一个表,‘分类’是cn_db连接文件的一个表
要执行
cn_sql.Execute("insert into 总库 select * from 分类")
or
cn_db.Execute("insert into 总库 select * from 分类")
怎么办?
[解决办法]
还是以ACCESS为例,其它数据库原理基本一样:
Dim cn1 As ADODB.Connection,cn2 As ADODB.Connection Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Dim param As ADODB.Parameter Dim sql As String '连接分类数据库 Set cn2 = New ADODB.Connection cn2.Open "连接分类字符串" '返回分类记录集 set rs=cn2.execute("select f1,f2,f3 from 分类 where ....") '插入总库 Set cn1 = New ADODB.Connection cn1.Open "连接总库字符串" while not rs.eof sql="insert into 总库(f1,f2,f3) values(@1,@2,@3)" Set cmd = New ADODB.Command cmd.ActiveConnection = cn1 cmd.CommandText = sql cmd.CommandType = adCmdText Set param = cmd.CreateParameter("@1", advarChar, adParamInput, 20, rs!f1) cmd.Parameters.Append param Set param = cmd.CreateParameter("@2", adInteger, adParamInput, 4, rs!f2) cmd.Parameters.Append param Set param = cmd.CreateParameter("@3", adDate, adParamInput, 8, rs!f3) cmd.Parameters.Append param cmd.Execute Set cmd = Nothing rs.movenext wend Set rs = Nothing Set cn2 = Nothing Set cmd = Nothing Set cn1 = Nothing