Re: SQL LOAD

Joseph Arcieri <73044.2177@CompuServe.COM> wrote: >I'm having some difficulty using LOAD or DBLOAD. Here's my problem. >I have a data file with 2 columns, | delimited, that I want to load/update >into the database. > >My statement reads... >load from "data.file" insert into database (column#1, column#2) where>columnX = columnX > >It's the "where columnX = columnX" that's giving me the trouble. I just >can't add the data to the end of the file, I need to update the database >with the "data.file" columns. Any help or suggestions would be >GREEATTLY appreciated! It's not clear exactly what you want to do. There is no "WHERE" clause with the INSERT statement - you simply do: load from "data.file" delimiter "|" insert into tab_1 (col_1, col_2)This will add a row in tab_1 for every line in the load file. If you want to UPDATE tab_1 with values in the load file, you must load into a temporary table and then update. In the following example, I'll assume col_1 is the primary key. -- create and load temp table CREATE TEMP TABLE tab_tmp (col_1 INTEGER, col_2 CHAR(12)); LOAD FROM "data.file" delimiter "|" INSERT INTO tmp_tab (col_1, col_2); -- update existing rows in tab_1 if they match in tmp_tab -- take some care not to wipe out tab_1.col_2 if not in tmp_tab UPDATE tab_1 SET col_2 = (SELECT col_2 FROM tmp_tab WHERE tmp_tab.col_1 = tab_1.col_1) WHERE tab_1.col_1 IN (SELECT tmp_tab.col_1 FROM tmp_tab); -- insert "new" rows (tmp_tab.col_1 doesn't match any tab_1.col_1) INSERT INTO tab_1 (col_1, col_2) SELECT tmp_tab.col_1, tmp_tab.col_2 FROM tmp_tab WHERE tmp_tab.col_1 NOT IN (SELECT tab_1.col_1 FROM tab_1); TTFN -- Mark Rafn dagon@halcyon.com http://www.halcyon.com/dagon/ Decadence is it's own reward.