Select INTO, UNION

[kcalanyuan]

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 !

 

[Mary]

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 !

 

[Daniel Crichton]

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

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 !

SELECT ... 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.

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.

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

 

[Mary]

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