Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

table updation

Posted on 2004-10-04
13
Medium Priority
?
174 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Accepted Solution

by:
sgayatri earned 300 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

916 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