RE: SQL Write?

Mike,
it's unfortunate to hear that your shop uses little or no SQL considering
the power it offers.  On the other hand, it's a testament to the good job
400's been doing for businesses all these years.

If you're putting records into an existing file you need to use sql INSERT
clause, i.e.:

INSERT INTO myFileBeingUpdated 
SELECT field1,field2,field3 
FROM myOtherFile 
WHERE field1 = 'something' AND 
      field2 = 33333       AND 
      field3 BETWEEN '08/08/05' AND CURRENT_DATE

If you're creating a new file and you're on V5R2 version of the OS or up,
you can use something like this to create it:

CREATE TABLE myNewFile AS 
  (SELECT field1,field2,field3 
   FROM myOtherFile 
   WHERE field1 = 'something' AND 
      field2 = 33333       AND 
      field3 BETWEEN '08/08/05' AND CURRENT_DATE)
WITH DATA

And if you're updating existing records in a table then you'd need to use
UPDATE clause, something like:

UPDATE myFile a SET (field1,field2,field3) = 
 (SELECT b.field1, b.field2, b.field3 
  FROM   myOtherFile b 
  WHERE  a.uniqueId = b.uniqueId)

Enough to chew on for the beginning :)

Elvis

-----Original Message-----
Subject: SQL Write?

I have a simple SQL question.  Nobody in my shop uses it except me, and 
the little I know I've learned on my own and from reading messages in this 
list.  I know how to create a cursor and fetch the records.  That's about 
all.  :)  So... my question:

I have a simple request to pull some records from a couple files meeting 
certain criteria and write it to a database file.  No calculations are 
needed.  I can easily do this using OpnQryF or an SQL cursor and fetch 
each record, then write each one sequentially, but I wanted to know if I 
had any other options.   Is it possible to use SQL to write all the 
records at once instead of having to fetch and write each record 
individually?

Thanks!

Mike