I have two databases with same column name, I want to combine all the
data into a temp table in the fly, after display the result then drop
the table, like below

dbA
FNo   FName  Amount
101    BBB     100
102    CCC     200

dbB
FNo   FName  Amount
101    BBB     400
102    CCC     500

dbtemp
FNo   FName  Amount
101    BBB     500
102    CCC     700

I used the following clause but failed,
SQL = "CREATE TABLE dbtemp (FNo varchar(10), FName varchar(100),
DAmount INT)"
SQL = " And SELECT FNo, FName, Amount INTO dbtemp IN 'dbA.mdb' FROM
'dbA'"
SQL = " And SELECT FNo, FName, Amount INTO dbtemp IN 'dbB.mdb' FROM
'dbB'"
Set rs = GetMdbRecordset( "dbtemp", SQL)

Can anyone help me ? Thanks !

After trying one whole day, I find a solution to make it work :

Set conn = GetMdbConnection( "dbtemp.mdb")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
SQL = "SELECT FNo, FName, Amount INTO Total  From
d:\Sites\fund\db1.mdb.MData"

     Cmd.CommandText = SQL
     Cmd.Execute

It really copy all the data from db1.mdb.Mdata  to dbtemp.Mdata, I really
see the data is inside. I know it is a very difficult job to be completed.

But then I try to copy another database using :
SQL= SQL & " Union All SELECT FNo, FName, Damount INTO Total  From
d:\Sites\fund\MDataUnhcr.mdb.MData"

It says the table is existed. Error !

Then I try many many clauses, all error !

In fact, we have over twenty companys with individual database, I just need
to sum up all sales amount for each colleague in all databases, it seems
very simple, is it possible using ASP ? Or may be I need to open each
database, then while not rs.eof, then rs("FNo") = rs1("FNo"), ......
rs.movenext, rs.close...open another again, finally get the answer. Or
transform all databases to tables and wedge into a database in order to make
"Union All", "Join" clauses more easy ? But this database may large than
20GB because each of this table contains various data and structure.

A very simple simple job, please help me !

Mary wrote  on Wed, 10 Jun 2009 00:27:29 +0800:

Show quoteHide quoteSELECT ... INTO creates a new table - that's why you get an error, because
the first one creates it in your temp database and the subsequent ones
cannot do so.

If you don't actually need the temp database table then you could use UNIONs
to select the data, eg.

SELECT FNo, FName, Damount FROM d:\Sites\fund\db1.mdb.MData
UNION ALL
SELECT FNo, FName, Damount FROM d:\Sites\fund\MDataUnhcr.mdb.MData


and if you need to do any aggregation on the data you can even use a
subquery, eg.


SELECT FNo, FName, SUM(Damount) FROM
(
SELECT FNo, FName, Damount FROM d:\Sites\fund\db1.mdb.MData
UNION ALL
SELECT FNo, FName, Damount FROM d:\Sites\fund\MDataUnhcr.mdb.MData
) as A
GROUP BY FNo, FName



and if you really do need to store this data in a table in dbtemp then this
should work:


SELECT FNo, FName, Damount
INTO Total
FROM
(
SELECT FNo, FName, Damount FROM d:\Sites\fund\db1.mdb.MData
UNION ALL
SELECT FNo, FName, Damount FROM d:\Sites\fund\MDataUnhcr.mdb.MData
) as A


You could use the

Thanks very much .  It works !! You are great !!


<kcalany***@yahoo.com.hk>
???????:b7c69333-7cac-4c51-9c16-72a72e144***@y7g2000yqa.googlegroups.com...
Show quoteHide quote

ASP.NET and HTML
pass stored procedure parameters in asp
create and send a form server side
Clueless in 80040e31 land
How to host a site on a closed network
Getting input in Multi Laguage
Validation of viewstate MAC failed.
II6 -> II7 with classic ASP
Include Files > asp to aspx - vbscript to vb.net
Replace characters in String