Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


ADO - 2 database query

Posted on 1999-07-26
Medium Priority
Last Modified: 2010-04-02
I am using VC++ 5 and ADO.  I want to insert all the records from Table1 of Database1 into Table1 of Database2 using one query if possible like:

INSERT INTO DB2.Table1 (Field1, Field2)
VALUES (SELECT Field1, Field2 FROM DB1.Table1)

Is there a way to do this?
Question by:joeslow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 10

Expert Comment

ID: 1201100
did you try the above query that you wrote? i'm not so good at database, but  you might try using while (!db.table.eof) {.....}


Expert Comment

ID: 1201101
This works only on SQL server.

INSERT INTO DB2..Table1 (Field1, Field2)
VALUES (SELECT Field1, Field2 FROM DB1..Table1)

INSERT INTO DB2.dbo.Table1 (Field1, Field2)
VALUES (SELECT Field1, Field2 FROM DB1.dbo.Table1)


Expert Comment

ID: 1201102
- The SQL statement looks odd. Try removing VALUES.
- Table names are DATABES.OWNER.NAME, so it should be DB2..MyTable

INSERT INTO DB2..Table1 (Field1, Field2)
SELECT Field1, Field2 FROM DB1..Table1
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 1201103
I don't think it will work with other databases. BTW, what database are you using?

Author Comment

ID: 1201104
I am using Access.  I don't have a "database" with ADO, only a connection...

Accepted Solution

Moin earned 300 total points
ID: 1201105
Firstly, For MS Access you need to have two different ADO connections connected to DB1 and DB2.

Next you have to get all the reords from Table1 of DB1 into a recordset of connection 1.
Then loop thru the recordset and use insert statement like this:
Psudo code follows:

recordSet1 of Connection1
recordSet2 of Connection2

while not recordSet1.Eof
  recordset2.Execute "insert into table1 (field1, field2)  values (recordset1('field1'), recordset2('field2')";

where table1 in insert statement is of DB2.


Expert Comment

ID: 1201106
Sorry, i made a typo...
The correct insert stmnt. in while block is :

recordset2.Execute "insert into table1 (field1, field2)  values (recordset1('field1'), recordset1('field2')";


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question