RE: SQL insert - all fields

Jeff,

INSERT INTO OLITMMST (SELECT A.*, CURRENT_DATE 
                      FROM DMITMMST A 
                      WHERE ACREC ='D');


HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeff Crosby
> Sent: Thursday, June 16, 2005 12:13 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: SQL insert - all fields
> 
> 
> Don't laugh when you read this. <g>
> 
> I'm trying to learn SQL with some simple places to use it, 
> like deleting
> records from files at month end.  Instead of RPG programs, I'm doing
> RUNSQLSTM from a CL program.  It's going well, but I have a 
> sticky one that
> I don't think lends itself to SQL very well.
> 
> Before a discontinued item is deleted from the item master 
> (DMITMMST) it is
> added to a file of old item numbers (OLITMMST) where it stays 
> for 24 months
> before the item number can be reused.  The record formats are 
> identical
> *EXCEPT* that OLITMMST has an additional field at the end of 
> the format
> called DLDATE which is the date the item was deleted.
> 
> What I want to do in the SQL script is:
> 
> --
> -- Add items marked for deletion to old items file
> --
> INSERT INTO OLITMMST (*) VALUES (SELECT * FROM DMITMMST WHERE ACREC =
> 'D',CURRENT DATE);
> --
> -- Remove items marked for deletion
> --
> DELETE FROM DMITMMST WHERE ACREC = 'D'; 
> 
> Every INSERT example and discussion I can find always lists 
> every field by
> name.  Isn't there a way to tell it "all fields" like the "*" 
> in SELECT
> statements?
> 
> Thanks.
> 
> -- 
> Jeff Crosby
> Dilgard Frozen Foods, Inc.
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
> 
> The opinions expressed are my own and not necessarily the 
> opinion of my
> company.  Unless I say so.
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>