Retrieving data from different Databases in Different Server

maya

DB1 --> SQLServer1.
DB2 --> SQLServer2.

I want to acess(read/write) DB1 Table data in DB2 and vice versa.

we achieved it through ADO Connection but due to n number of table's and huge volume of records it is time consuming and also while writing syntaticaly we need to retrieve field by field for each record in DB1 and insert in table of DB2.

Could anybody suggest best method of achieving it and tell the procedure by citing an example.

kriki

If the logic for selecting the records to read and inserting them is not to difficult, you can make a linked server and launch a stored procedure (or a sql-statement).
like :

INSERT INTO db2
SELECT fields 
FROM db1
WHERE...

Or if you are using SQL2008, you can also see the MERGE statement.

At this point we are pure in SQL, so you better check with someone who is familiar with SQL.
And also : this SET-based command will be a lot faster than doing it iteratively through NAV.