Solved

table updation

Posted on 2004-10-04
13
164 Views
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.
0
Comment
Question by:niranjanrampure
  • 4
  • 3
  • 2
  • +1
13 Comments
 
LVL 3

Expert Comment

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

Dom
0
 
LVL 3

Expert Comment

by:sgayatri
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.

0
 

Author Comment

by:niranjanrampure
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.

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
LVL 3

Accepted Solution

by:
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....
0
 

Author Comment

by:niranjanrampure
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 .
0
 
LVL 3

Expert Comment

by:domj
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.

Dom
0
 

Author Comment

by:niranjanrampure
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.
0
 
LVL 1

Expert Comment

by:JonGartzia
ID: 12225041
sgayatri is right.

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

Expert Comment

by:sgayatri
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
 compare,
if mismatch, insert the missings....


0
 

Author Comment

by:niranjanrampure
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

828 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