StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As far I know , the LOAD statement is limited to use together of INSERT statement. I pretty sure there a lot of ways to do this , I will suggest two way:</p> <p>In both cases, is supported only for database version >= 11.50 and have certain limitations like:</p> <ul> <li>The Merge works only if the two tables have 1 to 1 relationship</li> <li>The external table is limited to Database Server file system, not will access anything on client machine </li> </ul> <h2>SUGGESTION 1</h2> <p>Load into a temporary table and then use the <a href="http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_2030.htm" rel="nofollow">MERGE</a> statement.</p> <pre><code>create temp table tp01 ( cols.... ) with no log ; load from xyz.txt insert into tp01 ; merge into destTable as A using tp01 as B ON A.empID = B.empID WHEN MATCHED THEN UPDATE SET status = 'N' WHEN NOT MATCHED THEN INSERT (empid, status) values ( b.empid, 'N'); drop table tp01; </code></pre> <h2>SUGGESTION 2</h2> <p>Create a <a href="http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_2053.htm" rel="nofollow">external table</a> to you TXT file and then just use the MERGE or UPDATE using this table when needed.</p> <pre><code>create external table ex01 .... using ( datafile('file:/tmp/your.txt'), delimited ...); merge into destTable as A using ex01 as B ON A.empID = B.empID WHEN MATCHED THEN UPDATE SET status = 'N' WHEN NOT MATCHED THEN INSERT (empid, status) values ( b.empid, 'N'); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload