• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

ADO - 2 database query

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?
1 Solution
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) {.....}

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)

- 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I don't think it will work with other databases. BTW, what database are you using?
joeslowAuthor Commented:
I am using Access.  I don't have a "database" with ADO, only a connection...
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.

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')";

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now