Midrange News for the IBM i Community


Posted by: neilrh
A/P
Jackson, MI
Duplicate records for different key
has no ratings.
Published: 28 Nov 2012
Revised: 23 Jan 2013 - 1703 days ago
Last viewed on: 22 Sep 2017 (3126 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Duplicate records for different key Published by: neilrh on 28 Nov 2012 view comments(9)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Duplicate records for different key
Posted: 4 years 9 months 25 days 9 hours 30 minutes ago

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.

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Duplicate records for different key
Posted: 4 years 9 months 25 days 5 hours 48 minutes ago

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'

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Duplicate records for different key
Posted: 4 years 9 months 25 days 5 hours 26 minutes ago

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.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Duplicate records for different key
Posted: 4 years 9 months 24 days 12 hours ago

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.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Duplicate records for different key
Posted: 4 years 9 months 24 days 9 hours 23 minutes ago

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

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Duplicate records for different key
Posted: 4 years 9 months 24 days 8 hours 25 minutes ago

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.

Posted by: TFisher
Premium member *
Comment on: Duplicate records for different key
Posted: 4 years 9 months 24 days 6 hours 40 minutes ago

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.

 

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Duplicate records for different key
Posted: 4 years 9 months 24 days 5 hours 37 minutes ago
Edited: Thu, 29 Nov, 2012 at 13:48:27 (1758 days ago)

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;

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Duplicate records for different key
Posted: 4 years 9 months 20 days 11 hours 39 minutes ago

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;