The best way is to create Linked Server and connect
between two servers using that Link.
For example, if you want to Get info from database B and
insert them into database A, create a linked server in
database A that connects to database B and perform your
query in database A like this:
Insert into database1.dbo.table(field1, field2)
Select field1, field2
it will work perfect. BTY, it supports all transaction
that you create over two servers. I mean if any thing
happens during transaction, it rolls back all
transactions, IF you are using TRANSACTION in your code or
>As far as writing a query that will insert data from one
table in a database
>into a table in another database, that is easy....
>Insert into database2..table(field1, field2)
>Select database1..table.field1, database1..table.field2
>If you need to use data from two different servers, you
will need to create
>a connection and a recordset for the source server, then
create a separate
>connection and a command object for the destination
server. Cycle through
>each record in the source recordset appending data to the
>using the command object.
>If this is being done completely between SQL Servers, the
you can create
>Linked or Remote servers and reference them within SQL
>Hope this helps....
>> I am a VB novice and am trying to modify a program to
>> exexcute a SQL statement that uses two databases.
>> It uses a single bulk SQL INSERT statement to insert
>> into a table in one database that are read from a table
>> another database. Is this possible in VB? RDO?
>> If so, is it possible to execute a single SQL statement
>> that access two databases on different servers?
>> Is there a good documentation source for this topic?
>> Any help is appreciated.