Select INTO, UNION

Discussion in 'ASP General' started by kcalanyuan@yahoo.com.hk, Jun 8, 2009.

  1. Guest

    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 !
    , Jun 8, 2009
    #1
    1. Advertising

  2. Mary Guest

    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, Jun 9, 2009
    #2
    1. Advertising

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

    --
    Dan
    Daniel Crichton, Jun 10, 2009
    #3
  4. Mary Guest

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


    <>
    ???????:...
    >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, Jun 18, 2009
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marco Ippolito
    Replies:
    2
    Views:
    3,201
    Marco Ippolito
    May 19, 2004
  2. Matt Garman
    Replies:
    1
    Views:
    667
    Matt Garman
    Apr 25, 2004
  3. Peter Dunker

    union in struct without union name

    Peter Dunker, Apr 26, 2004, in forum: C Programming
    Replies:
    2
    Views:
    869
    Chris Torek
    Apr 26, 2004
  4. Ed
    Replies:
    5
    Views:
    334
    James Kanze
    Aug 13, 2008
  5. palmiere
    Replies:
    1
    Views:
    400
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page