-- Migrating_Data_Using_DSNLessConnection76.RMD
-- Migrating data between two databases using DSN-Less Connection
-- Using R:BASE 7.6 Database ODBC Driver
-- Author: A. Razzak Memon
-- Date Created: October 28, 2008
-- Related Database: MasterDB, ArchDB
-- Common table to migrate data in both databaes: Contact

-- Assure the connection of correct database
   IF (CVAL('DATABASE')) <> 'ArchDB' OR (CVAL('DATABASE')) IS NULL THEN
      CONNECT ArchDB IDENTIFIED BY NONE
   ENDIF
-- Make sure that previously SATTACHed table is detached
   SET ERROR MESSAGE 2038 OFF
   SDETACH tContact NOCHECK
   SET ERROR MESSAGE 2038 ON
-- Make sure that the previously DSN-Less Database is Disconnected
   SDISCONNECT ';Driver=R:BASE 7.6 Database Driver (*.rb1);dbq=MasterDB'
-- Now Connect the Database Using the DSN-Less Connection
   SCONNECT ';Driver=R:BASE 7.6 Database Driver (*.rb1);dbq=MasterDB'
-- SAttach Table(s)
   SATTACH Contact AS tContact USING ALL
-- Review all records in SAttached tContact table (MasterDB Database)
   BROWSE * FROM tContact
-- Review all records in actual Contact table (ArchDB Database)
   BROWSE * FROM Contact
-- Project a temporary table with non matching <> records in ArchDB
   SET ERROR MESSAGE 2038 OFF
   DROP TABLE tmpContact
   SET ERROR MESSAGE 2038 ON
   PROJECT TEMPORARY tmpContact FROM tContact USING ALL +
   WHERE ContID NOT IN (SELECT ContID FROM Contact)
   INSERT INTO Contact +
   (CustID,ContID,ContFName,ContLName,ContPhone, +
   ContFax,ContCell,ContPager,ContEMail,ContInfo, +
   LastContactDate) +
   SELECT +
   CustID,ContID,ContFName,ContLName,ContPhone, +
   ContFax,ContCell,ContPager,ContEMail,ContInfo, +
   LastContactDate +
   FROM tmpContact
-- Now review all records in actual Contact table (ArchDB Database)
   BROWSE * FROM Contact
-- Drop Temporary Table
   DROP TABLE tmpContact
-- Make sure that SATTACHed table is detached
   SDETACH tContact NOCHECK
-- Make sure that DSN-Less Database is Disconnected
   SDISCONNECT ';Driver=R:BASE 7.6 Database Driver (*.rb1);dbq=MasterDB'
   RETURN