RE: SQL duplicate a set of records within same file

Yes, Jeff, you can do this. In my example, I named the fields on the
insert statement; I believe that is a requirement to do things this way.

create table loyd/jeff (
batch int,
sequence int,
hours int)

insert into loyd/jeff (batch, sequence, hours)
values(1,1,10)

insert into loyd/jeff (batch, sequence, hours)
values(1,2,5)

insert into loyd/jeff (batch, sequence, hours)
values(1,3,12)

select * from loyd/jeff order by 1,2

....+....1....+....2....+....3....+....4....+.
BATCH SEQUENCE HOURS
1 1 10
1 2 5
1 3 12
******** End of data ********

insert into loyd/jeff
(batch, sequence, hours)
select 3, sequence, hours from loyd/jeff where batch = 1

3 rows inserted in JEFF in LOYD.

select * from loyd/jeff order by 1,2

....+....1....+....2....+....3....+....4....+.
BATCH SEQUENCE HOURS
1 1 10
1 2 5
1 3 12
3 1 10
3 2 5
3 3 12
******** End of data ********

HTH,
Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, July 31, 2007 13:27
To: 'Midrange Systems Technical Discussion'
Subject: SQL duplicate a set of records within same file

I don't know if this is possible, but I gotta ask.

I'm changing a formerly multimember DDS defined file into a single
member
DDL defined table by adding fields that keep batches separate. On
occasion,
we have the need to duplicate a batch. We were able to do this before
via
CPYF because we copied to a different member, so there was no need to
change
any field values.

What I essentially want to do is duplicate all records with KEY1, KEY2,
fld1, fld2, fld3, ... fldn to NEWKEY1, NEWKEY2, fld1, fld2, fld3, ...
fldn
within the same file. Since I'm handling a _set_ of records, it seemed
logical to try SQL for this. I know it takes an INSERT, but the
examples
I'm finding online seem to be specific to a particular database, or so
it
seems. It would also be nice to not have to name all the columns . . .