At 08:55 PM 10/27/2008, Marc wrote:
Is there an easy way to Unload data form a table with a note field
to a file then Import that data into another db where the note field
is <> an existing row in the new database?
Currently I load the data into a temp table and let the user compare
the data and decide to import the data row by row. Not very slick
and it sure takes a long time. I know there has to be a better way.
Marc,
Here is a simple but eloquent method of using a DSN-Less connection
when migrating and/or comparing data between two databases.
Assume your goal is to copy patient notes in column (NoteColumn)
with primary key column (RecordID) in a table PatientNotes of the
primary database (MasterDB) to another database (ArchDB) with similar
table and column names, etc.
Use the following steps to achieve your goal.
01. Start R:BASE 7.6 or Turbo V-8 for Windows
02. CONNect to the database that needs updated, for example ArchDB
CONNECT ArchDB
03. At the R> prompt, SCONNECT to MasterDB using the DSN-Less connection
-- Option 1
-- DSN-Less Connection Using Oterro 4.0 ODBC Driver
-- Make sure that all previously SATTACHed tables are detached
SDETACH ALL NOCHECK
-- Make sure that the previously DSN-Less Database is Disconnected
SDISCONNECT ';Driver=Oterro 4.0 Database Driver (*.rb1);dbq=MasterDB'
-- Now Connect the Database using the DSN-Less Connection
SCONNECT ';Driver=Oterro 4.0 Database Driver (*.rb1);dbq=MasterDB'
-- SAttach Table(s)
SATTACH PatientNotes AS tPatientNotes USING ALL
-- Option 2
-- DSN-Less Connection Using Oterro 8.0 ODBC Driver
-- Make sure that all previously SATTACHed tables are detached
SDETACH ALL NOCHECK
-- Make sure that the previously DSN-Less Database is Disconnected
SDISCONNECT ';Driver=Oterro 8.0 Database Driver (*.rx1);dbq=MasterDB'
-- Now Connect the Database using the DSN-Less Connection
SCONNECT ';Driver=Oterro 8.0 Database Driver (*.rx1);dbq=MasterDB'
-- SAttach Table(s)
SATTACH PatientNotes AS tPatientNotes USING ALL
-- Option 3
-- DSN-Less Connection Using R:BASE 7.6 ODBC Driver
-- Make sure that all previously SATTACHed tables are detached
SDETACH ALL NOCHECK
-- 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 PatientNotes AS tPatientNotes USING ALL
-- Option 4
-- DSN-Less Connection Using R:BASE 8.0 ODBC Driver
-- Make sure that all previously SATTACHed tables are detached
SDETACH ALL NOCHECK
-- Make sure that the previously DSN-Less Database is Disconnected
SDISCONNECT ';Driver=R:BASE 8.0 Database Driver (*.rx1);dbq=MasterDB'
-- Now Connect the Database Using the DSN-Less Connection
SCONNECT ';Driver=R:BASE 8.0 Database Driver (*.rx1);dbq=MasterDB'
-- SAttach Table(s)
SATTACH PatientNotes AS tPatientNotes USING ALL
04. Insert the <> rows in ArchDB
-- you may customize this command as you wish
INSERT INTO PatientNotes (RecordID,NoteColumn) +
SELECT RecordID,NoteColumn FROM tPatientNotes +
WHERE RecordID NOT IN (SELECT RecordID FROM PatientNotes)
-- This will insert non-matching rows in PatientNotes table of
ArchDB from sattached tPatientNotes table in MasterDB
05. SDetach tPatientNotes table
SDETACH tPatientNotes NOCHECK
06. SDisconnect DSN-Less connection
SDISCONNECT ';Driver=Oterro 4.0 Database Driver (*.rb1);dbq=MasterDB'
or
SDISCONNECT ';Driver=Oterro 8.0 Database Driver (*.rx1);dbq=MasterDB'
or
SDISCONNECT ';Driver=R:BASE 7.6 Database Driver (*.rb1);dbq=MasterDB'
or
SDISCONNECT ';Driver=R:BASE 8.0 Database Driver (*.rx1);dbq=MasterDB'
That's all there is to it!
Of course, the upcoming R:eXtreme (v9.0) will let you drag-and-drop
tables, views, stored-procedures, forms, reports and labels between
any number of connected databases. Can you imagine that feature in
a true-relational database?
Very Best R:egards,
Razzak.
