Insert Into from two different DBs

[Phailak]

Hail,

I'm trying to query one database and insert the result into another table from another DB. I use DAO in VB, and this is what I wrote so far, but I can't seem to get the IN syntax correctly:

Dim DBref As Database
Set DBref = OpenDatabase("", 512, false, "ODBC;DSN=WebBillingRefiner&quot

DBref.Execute (&quot;INSERT INTO dbo.PatDataMigration.SRB_ModeratorMigration2 (SRB_ModID) SELECT OSS_SRB_MODERATOR_ID FROM C_OSS_SRB_LINK WHERE OSS_RES_FLG = 0 AND OSS_SRB_MODERATOR_ID <> 1&quot



BTW, I have reference DAO 3.6 and WebBillingRefiner points to a database that's under the same server as PatDataMigration so shouldn't I be able to use both?

Phailak

 

[NoCoolHandle]

What sort of database platform are you using?
SQL Server?

 

[Phailak]

Yes...

 

[Phailak]

Hail,

Here's the solution that I got from MS site:

DBref.Execute (&quot;INSERT INTO [odbc;dsn=PatDataMigration].SRB_ModeratorMigration2 (SRB_ModID) SELECT OSS_SRB_MODERATOR_ID From C_OSS_SRB_LINK WHERE OSS_RES_FLG = 0 AND OSS_SRB_MODERATOR_ID = 113742&quot

Important is the ODBC connection:
[odbc;dsn=PatDataMigration].SRB_ModeratorMigration2

Phailak

 

[NoCoolHandle]

My belief is that you should probably go with Phailak, especially if you are using dao.. It has been too long for me to comment on DAO code.

However.. Just 2 thoughts.

1. Change to ADO. It is free, easier to use and works with just about any programming tool. It is also very powerful and allows you to mirate almost any technology to XML and disconnected recordsets..

2. If your databases are on the same server, SQL Server 7 and beyond support 4 part and 3 part naming schemas that reduce your need for more than a single connection.

You could simply use syntax like &quot;insert into dbo.table1 select col1,col2,col3 from database2.dbo.othertable where col4 = x&quot;

Where table1 is in the database that the current(active) connection is connected to.

To span Servers or Instanaces, just set up the source server or target server as &quot;linked servers&quot; and then use the server name as part of the syntax in your insert or select clause..

Basic Naming Schema = ServerName.DatabaseName.OwnerName.ObjectName

to which you can remove from the left hand side all the way to the right.

1 more note. If you are using ServerName as a part of the naming schema, you MUST specify the acutal object owner.

I.E.
Use &quot;insert into dbo.table1 select col1,col2,col3 from OtherServer.database2.dbo.othertable where col4 = x&quot;
and not
&quot;insert into dbo.table1 select col1,col2,col3 from OtherServer.database2..othertable where col4 = x&quot;