Solved

table updation

Posted on 2004-10-04
13
161 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Accepted Solution

by:
sgayatri earned 75 total points
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 3

Expert Comment

by:domj
Comment Utility
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
Comment Utility
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
Comment Utility
sgayatri is right.

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

Expert Comment

by:sgayatri
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

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…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now