VB6 insert into table from Recordset

Question

There are one view and one table

both has truly the same columns

but they are in diffrent servers

what I want to do is like below

cn1.ConnectionString = "Server1"
cn2.ConnectionString = "Server2"
sql = "SELECT * FROM VIEW"
Set rs.1ActiveConnection = cn1
rs1.Open sql, cn1
sql = "INSERT INTO table SELECT * FROM view"
cn2.Execute (sql)

I can access to view by cn1, but table by cn2

So this cant be done

I want to know how can it be done

table and view are exactly same

I searched a lot, but there was no good examples for me

I think there are two ways of doing it

inserting recordset into table or inserting each of field to another

easy example would be very helpful thank you

This does not "insert into a table from a recordset". It just runs an insert query. — StayOnTarget, Aug 20 '18 at 11:23
Have a look at this answer and see if it helps any https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server If you are running SQL 2008+ you could add the server name, database name and user name to the select statement. You will need to make sure the login you are using in the connection can access the other server. Your sql would be something like "Insert into table select * from [the server name].[the database name].[the schema name (possibly dbo)].View" — lardymonkey, Aug 20 '18 at 13:04

score 0 · Accepted Answer · answered Aug 22 '18 at 06:58

This won't work because, even though you are pulling the records from your first server into your recordset, you are trying to insert directly from the view, rather than inserting from the recordset.

You can loop through your recordset, and insert the records one by one into the other database, but that's a lot of round trips and is very slow. A better way is use UpdateBatch to insert from the recordset all in one go. Here's the basic idea:

cn1.ConnectionString = "Server1"
cn2.ConnectionString = "Server2"
sql = "SELECT * FROM VIEW"
Set rs.ActiveConnection = cn1

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient 'you need a client-side recordset

rs1.Open sql, cn1, adLockBatchOptimistic 'you need to set the locktype to this

Set rs.ActiveConnection = cn2 'now you're connecting your recordset to the other server
rs.UpdateBatch                'and updating the database all in one batch

That should get you started.

VB6 insert into table from Recordset

1 Answers1