table updation

in my vb programe i have 2 ado connections pointing to two different database.both data base have same tables with same names and structure.let say one db to be souce and  other to be destination.i want to check if both tables are simillar,if the source table in source db has more records then insert the record if the destination table has the record but is not simillar to the source record then update that record. i tried comparing one one record but  the program becomes too slow when the tables are very large. got any idea that i can select directly those records which are present in destination db  ie have the same primary key value  so that i can create an update query.and the records not present in destination db.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

sgayatriConnect With a Mentor Commented:
You can add a column to your source table,
say row_modified default value '0'.
Update this column to some value say '1' whenever a row changes.
It would be now easier to get those rows for updation by filtering on row_modified column

select * from db where row_modified = '1'

Regarding Insert, my previous post should do....
dont bother comparing records just run an update query for the existing records and an insert query for all the new stuff.

If you are particular to know if your source has more rows than the destination, then
take count of rows of both the tables
compare them
if matching leave
else go for insertion.

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

niranjanrampureAuthor Commented:
domj --u mean to say that i should update all records ---but thats not good ---i want to update only those rows which do not have simillar field values --and secondly how i am to get new records without comparing.

niranjanrampureAuthor Commented:
i have to perform the operation on 15 tables it is not possible to modify all the 15 tables  as i dont now hoe adding a field will affect my other stored procedures .
You need to use an update query like this:

Update table1 set table1.field=table2.field where table1.index=table2.index

then you use an insert query like this:

insert into table1 (table1.field1, table1.field2) values (table2.field1, table2.field2) where table2.index not in (select index from table1)

The first will update fields where the index already exists. The second will insert only fields where the index is not found in the new table.

niranjanrampureAuthor Commented:
domj --- this wont work here cause my tables are on two differnt database on two different servers,secondly i cant link my servers as i dont have permissionns to do so.   also is there any practial way of comaring two records may be their binary value.
sgayatri is right.

Use a field to store whether the rows are new, modified or not.
To my knowledge, there is no way of comparing recordsets, except for doing record by record.
For each record
 1. concatenate both recordsets
     r1 = rs1(0)+rs1(1)+rs1(2)...+rs1(n)
     r2= rs2(0)+rs2(1)+.....rs2(n)

 2. Now string compare r1 with r2

This should solve your update problem

As I wrote earlier,
Take count of records from both the tables
if mismatch, insert the missings....

niranjanrampureAuthor Commented:
cant the records be compared in some binary format--ie a binary number representing the entire record this way comparison and searching will be fast
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.

All Courses

From novice to tech pro — start learning today.