Hi Razzak
Thanks, I tried the Insert method but get an error about
you can't have the destination table in the where clause
or something like that. Also, I am hitting a snag on
Loading a table from a file.
No big deal, when I get more time to sit down and work
through this I should be able to get it to work.
My time is limited because I have to finish dealing with
putting out a fire, literally!
Thanks again
Marc
----- Original Message -----
From: "A. Razzak Memon" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Monday, October 27, 2008 8:18 PM
Subject: [RBASE-L] - Re: Import & Export note field
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.
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
