We help IT Professionals succeed at work.

verify if record exist before updating (migration vb program)

441 Views
Last Modified: 2013-12-25
Hi Guys

I have vb program to migrate records from old db to a new model.
This is the situation
tbl-Old
ID  CustID     Location
1          1        Newark
2          1        Denver
3          1        NY
4          1        NY
5          1        Newark


New-Model
ID   CustID   Location
1        1          Newark
2        1          Denver
3        1          NY

This is how it should be transfer, so before i update the new record, i have to verify if the CustID and Location already exist
I have the code that will transfer everything without comparing or verifying is it exist

Any comments  
Comment
Watch Question

Fire SEELCT CustID,  Location FROM tbl-Old and New-Model separately WHERE ID = 1, 2, 3 etc. compare them in code using Cursor/ResultSet/Iterator and if same then do not migrate else go ahead. For this if databases are different does not matter in single Access, different access in different systems as long as you maintain separate connection objects/contexts from application to them.

The other way is to let the code be as is developed and just introduce error/exception handling code where by if constraint violation prevents the migration, then dummy handle the vilation error/exception. It is not just a question of CustID and Location already existing but here more a question of PK ID in source and target both being present before data migration takes place.

If only one connection is possible at a time like in stored procedures, then link the databases/their tables like we have db link in Oracle, nicknames in DB2 and four part name in SQL Server and Sybase i.e. server.db.schema/owner.object.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Good. Fire a select query from both old and new tables while passing columns as parameters to your function so that if boolean returned is TRUE, then do not carry out the migration of these records else do so for the rest.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.