Re: SQL LOAD

In article <3q2qb7$fa7@news1.halcyon.com>, dagon@coho.halcyon.com (Mark Rafn) says: > >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. You could also create a procedure either using the same algorithm or creating an algorithm to shift around the values in the same table CREATE TABLE origtable ( id key int not null, //id =0 for original data, 1 for new data col1 int, col2 varchar(240), tempcol1 int, tempcol2 varchar(240) ); CREATE PROCEDURE update LOAD FROM "data.file" delimiter "|" INSERT INTO (id, tempcol 1, tempcol2); UPDATE origtable SET col1 = (SELECT * from origtable.tempcol1 WHERE id = 1 ); UPDATE origtable SET col2 = (SELECT * from origtable.tempcol2 WHERE id = 1 ); Gail Ferreira wadelich@ainet.com (gail ferreira) >Always expect the unexpected.