或用ado的NextRecordset属性得到多个结果集 Dim cmd As New ADODB.Command Dim rs As ADODB.Recordset Cmd.ActiveConnection = "DSN=MySamples;UID=sa" Cmd.CommandText = "MyNextProc" Cmd.CommandType = adCmdStoredProcSet rs = Cmd.Execute() While Not rs Is Nothing If (Not rs.EOF) Then Debug.Print rs(0) End If Set rs = rs.NextRecordset() Wend
select * from table1 union all select * from table2 union all select * from table3 union all select * from table4类似此,对总表做个游标,循环出上面的语句即可。
下级表的表结构是否一致? 一致: declare @cTableName varchar(20) declare @cSQL varchar(2000) declare @cCondition varchar(2000) declare @n intset @cCondition = ' where 1>0' -- 替换查询条件 set @n = 0declare curTable cursor for select cTableName from A open curTable fetch next from curTable into @cTableName while (@@FETCH_STATUS = 0) begin if (@n=0) set @cSQL = 'select * from ' + @cTableName + ' ' + @cCondition else set @cSQL = @cSQL + ' union select * from ' + @cTableName + ' ' + @cCondition set @n = @n + 1 fetch next from curTable into @cTableName end close curTable deallocate curTableprint @cSQL:_)
直接使用MSDataShape即可set cn=Server.CreateObject("ADODB.Connection") cn.Provider = "MSDataShape" cn.Open "xxx"strSQL="SHAPE {select * from 主表} APPEND ({select * from 子表} as rsChildTable RELATE 主表k1 TO 子表k2)"rs.Open strSQL,cn,0,1do while not rs.eof rsChild=rs.fields("rsChildTable").value do while not rsChild.eof rsChild.movenext loop rs.movenext loop
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset Cmd.ActiveConnection = "DSN=MySamples;UID=sa"
Cmd.CommandText = "MyNextProc"
Cmd.CommandType = adCmdStoredProcSet rs = Cmd.Execute()
While Not rs Is Nothing
If (Not rs.EOF) Then
Debug.Print rs(0)
End If
Set rs = rs.NextRecordset()
Wend
union all
select * from table2
union all
select * from table3
union all
select * from table4类似此,对总表做个游标,循环出上面的语句即可。
你可以看看:
http://expert.csdn.net/Expert/FAQ/FAQ_Index.asp?id=133244
一致:
declare @cTableName varchar(20)
declare @cSQL varchar(2000)
declare @cCondition varchar(2000)
declare @n intset @cCondition = ' where 1>0' -- 替换查询条件
set @n = 0declare curTable cursor for
select cTableName from A open curTable
fetch next from curTable into @cTableName while (@@FETCH_STATUS = 0)
begin
if (@n=0)
set @cSQL = 'select * from ' + @cTableName + ' ' + @cCondition
else
set @cSQL = @cSQL + ' union select * from ' + @cTableName + ' ' + @cCondition set @n = @n + 1
fetch next from curTable into @cTableName
end close curTable
deallocate curTableprint @cSQL:_)
cn.Provider = "MSDataShape"
cn.Open "xxx"strSQL="SHAPE {select * from 主表} APPEND ({select * from 子表} as rsChildTable RELATE 主表k1 TO 子表k2)"rs.Open strSQL,cn,0,1do while not rs.eof
rsChild=rs.fields("rsChildTable").value
do while not rsChild.eof
rsChild.movenext
loop rs.movenext
loop