Updating a Database Table from a cursor.

[Pesteo]

Here's the deal, I have got a text file that contains information that needs to be inserted into a table embedded in a database with 500,000 records...

The text file is set up as follows:
"PK", "Date", "ID-Name", "Column_name1", "Data1", "Column_name2", "Data2"...

There are 150 column names and then the corresponding data.

To accomplish the task, I built a cursor...Appended the text file to that cursor, then alter the cursor to remove the column names....

New View:
PK Data1 Data2 Data3... in table format

Now I have to update the table in my database with the information in the cursor....

My original thought was to scan through the cursor record by record and update the table based on the primary key....

For some reason, I can't get it to do that.....
This is the program I wrote in ugly format.....

CLOSE ALL
CLEAR ALL

CREATE CURSOR cursor_org (f1 c(14),f2 c(14),f3 c(14), f4 C(25), f5 C(8), f6 C(25), f7 C(8), f8 C(25), f9 C(8), f10 C(25), f11 C(8),;
f12 C(25), f13 C(8), f14 C(25), f15 C(8), f16 C(25), f17 C(8), f18 C(25), f19 C(8), f20 C(25), f21 C(8), f22 C(25), f23 C(8), f24 C(25), f25 C(8),;
f26 C(25), f27 C(8), f28 C(25), f29 C(8), f30 C(25), f31 C(8), f32 C(25), f33 C(8), f34 C(25), f35 C(8), f36 C(25), f37 C(8), f38 C(25), f39 C(8),;
f40 C(25), f41 C(8), f42 C(25), f43 C(8), f44 C(25), f45 C(8), f46 C(25), f47 C(8), f48 C(25), f49 C(8), f50 C(25), f51 C(8), f52 C(25), f53 C(8),;
f54 C(25), f55 C(8), f56 C(25), f57 C(8), f58 C(25), f59 C(8), f60 C(25), f61 C(8), f62 C(25), f63 C(8), f64 C(25), f65 C(8), f66 C(25), f67 C(8),;
f68 C(25), f69 C(8), f70 C(25), f71 C(8), f72 C(25), f73 C(8), f74 C(25), f75 C(8), f76 C(25), f77 C(8), f78 C(25), f79 C(8), f80 C(25), f81 C(8),;
f82 C(25), f83 C(8), f84 C(25), f85 C(8), f86 C(25), f87 C(8), f88 C(25), f89 C(8), f90 C(25), f91 C(8), f92 C(25), f93 C(8), f94 C(25), f95 C(8),;
f96 C(25), f97 C(8), f98 C(25), f99 C(8), f100 C(25), f101 C(8), f102 C(25), f103 C(8), f104 C(25), f105 C(8), f106 C(25), f107 C(8), f108 C(25), f109 C(8),;
f110 C(25), f111 C(8), f112 C(25), f113 C(8), f114 C(25), f115 C(8), f116 C(25), f117 C(8), f118 C(25), f119 C(8), f120 C(25), f121 C(8), f122 C(25), f123 C(25),;
f124 C(8), f125 C(25))
APPEND FROM "D:\Projects\Health Lab\org.txt" DELIMITED

ALTER TABLE cursor_org DROP f2
ALTER TABLE cursor_org DROP F3
ALTER TABLE cursor_org DROP F4
ALTER TABLE cursor_org DROP F6
ALTER TABLE cursor_org DROP F8
ALTER TABLE cursor_org DROP F10
ALTER TABLE cursor_org DROP F12
ALTER TABLE cursor_org DROP F14
ALTER TABLE cursor_org DROP F16
ALTER TABLE cursor_org DROP F18
ALTER TABLE cursor_org DROP F20
ALTER TABLE cursor_org DROP F22
ALTER TABLE cursor_org DROP F24
ALTER TABLE cursor_org DROP F26
ALTER TABLE cursor_org DROP F28
ALTER TABLE cursor_org DROP F30
ALTER TABLE cursor_org DROP F32
ALTER TABLE cursor_org DROP F34
ALTER TABLE cursor_org DROP F36
ALTER TABLE cursor_org DROP F38
ALTER TABLE cursor_org DROP F40
ALTER TABLE cursor_org DROP F42
ALTER TABLE cursor_org DROP F44
ALTER TABLE cursor_org DROP F46
ALTER TABLE cursor_org DROP F48
ALTER TABLE cursor_org DROP F50
ALTER TABLE cursor_org DROP F52
ALTER TABLE cursor_org DROP F54
ALTER TABLE cursor_org DROP F56
ALTER TABLE cursor_org DROP F58
ALTER TABLE cursor_org DROP F60
ALTER TABLE cursor_org DROP F62
ALTER TABLE cursor_org DROP F64
ALTER TABLE cursor_org DROP F66
ALTER TABLE cursor_org DROP F68
ALTER TABLE cursor_org DROP F70
ALTER TABLE cursor_org DROP F72
ALTER TABLE cursor_org DROP F74
ALTER TABLE cursor_org DROP F76
ALTER TABLE cursor_org DROP F78
ALTER TABLE cursor_org DROP F80
ALTER TABLE cursor_org DROP F82
ALTER TABLE cursor_org DROP F84
ALTER TABLE cursor_org DROP F86
ALTER TABLE cursor_org DROP F88
ALTER TABLE cursor_org DROP F90
ALTER TABLE cursor_org DROP F92
ALTER TABLE cursor_org DROP F94
ALTER TABLE cursor_org DROP F96
ALTER TABLE cursor_org DROP F98
ALTER TABLE cursor_org DROP F100
ALTER TABLE cursor_org DROP F102
ALTER TABLE cursor_org DROP F104
ALTER TABLE cursor_org DROP F106
ALTER TABLE cursor_org DROP F108
ALTER TABLE cursor_org DROP F110
ALTER TABLE cursor_org DROP F112
ALTER TABLE cursor_org DROP F114
ALTER TABLE cursor_org DROP F116
ALTER TABLE cursor_org DROP F118
ALTER TABLE cursor_org DROP F120
ALTER TABLE cursor_org DROP F122
ALTER TABLE cursor_org DROP F124

OPEN "Database Path"...

SCAN
UPDATE wqorg
SET
dichloroethylene_1_1 = F5, trichlorothane_1_1_1 = F7, trichlorothane_1_1_2 = F9, dichloroethane_1_2 = F11, diochloropropane_1_2 = F13,;
trichlorobenzene_1_2_4 = F15, benzene = F17, carbon_tetrachloride = F19, dichloroethylene_cis_1_2 = F21, methylene_chloride,;
ethylbenzene = F23, chlorobenzene = F25, o_dichlorobenzene = F29, para_dichlorobenzene = F31, styrene = F33, tetrachloroethylene = F35,;
toluene = F37, dichloroethylene_trans_1_2 = F39, trichloroethylene = F41, vinyl_chloride = F43, total_xylenes = F45, bromobenzene = F47,;
bromochloromethane = F49, bromodichloromethane = F51, bromoform = F53, bromomethane = F55, chloroethane = F57, chloroform = F59, chloromethane = F61,;
chlorotoluene_2 = F63, chlorotoluene_4 = F65, dibromo_3_chloropropane_2 = F67, dibromochloromethane = F69, dibromoethane_2_1 = F71, dibromomethane = F73,;
dichlorobenzene_1_3 = F75, dichlorodifluoromethane = F77, dichloropropene_1_1 = F79, dichloropropene_trans_1_3 = F81, hexachlorobutadiene = F83, isopropyl_toluene = F85,;
isopropylbenzene = F87, n_butylbenzene = F89, n_propylbenzene = F91, naphthalene = F93, sec_butylbenzene = F95, tert_butylbenzene = F97, tetrachloroethane_1_1_1_2 = F99,;
tetrachloroethane_1_1_2_2 = F101, trichlorobenzene_1_2_3 = F103, trichlorofluoromethane = F105, trichloropropane_1_2_3 = F107, trimethylbenzene_4_2_1 = F109, trimethylbenzene_5_3_1 = F111,;
total_trihalomethanes = F113, mp_xylene = F115, o_xylene = F117, dichloroethane_1_1 = F119, dichloropropane_3_1 = F121, dichloropropene_cis_1_3 = F123, dichloropropane_2_2 = F125
WHERE sample_number = F1
END SCAN

If you have any suggestions, I'm at a loss....
Thanks....

 

[wgcs]

Instead of the UPDATE ... SET command (which only changes existing records), I think you want this command:

INSERT INTO wqorg (sample_number, dichloroethylene_1_1, .... ) ;
  VALUES ( F1, F5, .....  )

.. which adds a new record.

However, because you have so many fields, the field names get separated rather far from the values being put into them, and it may be better to use this method:

SELECT wqorg
APPEND BLANK
REPLACE Sample_number        with F1, ;
        dichloroethylene_1_1 with F5, ;
        .....
REPLACE hexachlorobutadiene  with F83, ;
        ... more fields here ...

 

[Pesteo]

The primary key that is used to reference the record is already in the table...
I just need to add the rest of the data to that record....

The bigger problem that I am also having the problem of keeping the cursor open while opening the database to update the records....

Thanks....

 

[Pesteo]

What does the routine that you gave me do? Is there anyway that I can select the exact record from the table, and then
replace the null values with the new data that is in the cursor?

 

[wgcs]

Ok, since the primary key is already there, then you were on the right track with UPDATE, which is the equivalent of SEEK, REPLACE.

If your Primary Key is Sample_Number, and the index is called Sample_Number, then do this:

CREATE CURSOR cursor_org (f1 c(14), .... )
APPEND FROM "D:\Projects\Health Lab\org.txt" DELIMITED

ALTER TABLE cursor_org DROP f2
.
.
.

SELECT 0
OPEN "Database Path"...
USE wqorg ORDER Sample_Number
SELECT Cursor_ORG
SCAN
  SELECT wqorg
  SEEK (cursor_org.f1)
  if not found()
    APPEND BLANK
    REPLACE Sample_Number WITH F1
  endif
  
  replace dichloroethylene_1_1 WITH F5, ;
          trichlorothane_1_1_1 WITH F7, ;
          ....
  * You can use multiple REPLACE commands
  *    to make the lines of code shorter... 
  * Experiment with how many fields can be replaced at once
  *.. I don't know the limits.
  REPLACE trichlorothane_1_1_2 WITH F9, ;
          ....
ENDSCAN