table updation

Posted on 2004-10-04
Last Modified: 2010-05-02
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.
Question by:niranjanrampure
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1

Expert Comment

ID: 12224286
dont bother comparing records just run an update query for the existing records and an insert query for all the new stuff.


Expert Comment

ID: 12224365
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.


Author Comment

ID: 12224643
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.

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.


Accepted Solution

sgayatri earned 75 total points
ID: 12224849
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....

Author Comment

ID: 12224856
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 .

Expert Comment

ID: 12224914
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.


Author Comment

ID: 12225024
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.

Expert Comment

ID: 12225041
sgayatri is right.

Use a field to store whether the rows are new, modified or not.

Expert Comment

ID: 12236187
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....


Author Comment

ID: 12236828
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question