RE: Update a column in a table from a flat file

Database file rsides on an MS-SQL server. The text file will reside on a pc,
likely mine. It is produced by running a report on the MS-SQL database, and
linking, via a subreport, to System i. It is the data retrieved from System i
that will be used to update the MS-SQL database.

I saw an earlier post suggesting the IMPORT command. That has potential.

Ideally, this would be a one time data push. Future updates would be a LOT
smaller and would be done manually.

John McKee

Quoting vhamberg@xxxxxxxxxxx:

John

Where does this file live? On the iSeries or on the PC? You can't easily run SQL against text files - although there IS a text ODBC driver in Windows that you could use - you end up defining the row - just like I-specs, sort of.

Vern
-------------- Original message --------------
From: John McKee <jmmckee@xxxxxxxxxxxxxx>

My definitiion of a flat file is just a plain text file. No indexes,
not a PF. It is the output generated from Crystal Report Writer. How
would I specifity
this non database file? The nonkeyed file will contain two columns: the row
identifier in FACILITY_PATIENT, and the value to change a selected column to.


UPDATE FACILITY_PATIENT SET FMRN = xxx
WHERE MEDICAL_RECORD_NUMBER = yyy

The yyy value is the first column in the nonkeyed file. The xxx value is the
value to apply to the named column of the selected row.

But, how is this non SQL file identified?

John McKee

Quoting vhamberg@xxxxxxxxxxx:

> Alan
>
> You know all this, I'm just piggy-backing on your post.
>
> If "flat file" means a one-field record, substr(field, 5, 10) works -
> the contents starting at position 5 for 10 characters.
>
> If a "non-keyed file", no different from any other physical file.
>
> I really wish we'd stop talking about flat files anymore - that term
> is pretty meaningless, seems to me, in this day and age. A PF is a PF
> is a table is a table.
>
> HTH
> Vern
>
> -------------- Original message --------------
> From: Alan Shore
>
>> John,
>> Without sounding sarcastic, YOU tell the system how to update based upon
>> whatever match conditions apply.
>> When you said "flat" file, do you mean :-
>> a non-keyed file?
>> a file with one field in the record?
>>
>>
>> Alan Shore
>>
>> NBTY, Inc
>> (631) 244-2000 ext. 5019
>> AShore@xxxxxxxx
>> "If you're going through Hell, keep going" - Winston Churchill
>> midrange-l-bounces@xxxxxxxxxxxx wrote on 02/13/2008 10:46:30 AM:
>>
>> > I am not at all proficient in SQL. I was not aware that SQL could update
>> a
>> > table, based on a flat file. How would SQL know where the values where
>> in the
>> > flat file?
>> >
>> > John McKee
>> >
>> > Quoting "John Arnold (MFS)" :
>> >
>> > > For the column where you want the new value use,
>> > >
>> > > Case when mrc = yyy then xxx end as id
>> > >
>> > > In your select statement that creates the flat file.
>> > >
>> > >
>> > > John Arnold
>> > > (301) 354-2939
>> > >
>> > >
>> > > -----Original Message-----
>> > > From: midrange-l-bounces@xxxxxxxxxxxx
>> > > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John McKee
>> > > Sent: Tuesday, February 12, 2008 11:34 PM
>> > > To: Midrange Systems Technical Discussion
>> > > Subject: Update a column in a table from a flat file
>> > >
>> > > My earlier question has been resolved. Can't skip a key field in a
>> > > database and get decent performance.
>> > >
>> > > Now, I wondering:
>> > >
>> > > Run the Crystal report. Export the rsssults to a flat file. First
>> > > field would be a complete primary key to the MS-SQL database. Third
>> > > field would be the new value for a single specified field.
>> > >
>> > > One possibility would be to modify the text file to read like this:
>> > >
>> > > update clinical set id= xxx where mrc=yyy
>> > >
>> > > The above line would be modified so that a text file would have several
>> > > thousand individual update commands. The file would be input to a
>> > > command line program, name eludes me for the moment.
>> > >
>> > > Is there a way to do all the updates with a single command?
>> > >
>> > > John McKee
>> > >