Using the DEFAULT keyword you can avoid the field list and embed the list of fields in the VALUES keyword, adding DEFAULT where you want to avoid the data being copied--but that still gets you entering the field list in the VALUES clause.

Not sure if this is what your're looking for, but here's an example of SQL I use when starting a new season.  It clones the setup reqords for last season's departments as the starting point for the new season.  It simply pulls last season's rows and substitutes the new season ID on the insert.

Insert into SEAS_DEPTS(
         ID_COMPANY,
         ID_SHP_SEAS,
         ID_LOB,
         ID_DEPT)
SELECT   ID_COMPANY,
         'SEAS2012',
         ID_LOB,
         ID_DEPT
FROM     SEAS_DEPTS
WHERE    ID_SHP_SEAS = 'SEAS2011'

That's the beginning of where I'm coming from, Steve. And it's an ok method for a file with few columns, it's when you need to duplicate rows in a file where you have a couple of hundred columns.

And no, Bob, in this case DEFAULT doesn't help. For example: Customer A buys Customer B - can you please duplicate all of the pricing overrides currently effective for Customer A to Customer B. I don't want records duplicated with DEFAULT values, I want the exact image of Customer A records created for Customer B.

Is this just SQL, or embedded SQL? Pure SQL you pretty much have to list them all.

With embedded SQL, you could read into a DS that's based on the record format, change only the DS subfields for keys, then INSERT from the DS.

Or, mwahaha, this is pretty much trivial with native I/O.

DaleB, I need a "like" button on your last comment.

I thought so - would be nice to have a shorthand method, but guess my colleague needs to type all 100 column names into his insert.

What I normally do is insert the records that I want to copy into a QTEMP file, update the fields that I want to update for the new records in QTEMP, then insert them back into the file from QTEMP.

SteveCCNJ "Likes" the Fish Method as an alternate.  !--script-- it like:

CREATE TABLE WrkTable LIKE PriceTable;

INSERT INTO WrkTable SELECT * FROM PriceTable WHERE CustFld = 'OLDCUST';

UPDATE WrkTable SET CustFld TO 'NEWCUST';

INSERT INTO PriceTable SELECT * FROM WrkTable;

DROP WrkTable;

If you only need do it once, not in a loop, you can combine the first two statements:

CREATE TABLE WrkTable
AS (SELECT * FROM PriceTable WHERE CustFld = 'OLDCUT') WITH DATA;

Or, even better:

DECLARE GLOBAL TEMPORARY TABLE WrkTable
AS (SELECT * FROM PriceTable WHERE CustFld = 'OLDCUT') WITH DATA;