I need to duplicate one line of a specific table, but in same duplicated line I have to change the primary key. In my case the primary key is a three digit year. Example: this year would be 221, next year 222.
I'm using a record with a primary key of 999 that I'll duplicate. Now I need to make it dynamic (doesn't has to be dynamic while testing), so that I can use it for more than one table only, because it will be called from a procedure in RPGLE.
I've used a very static method for that before:
INSERT INTO Table
SELECT PK -- the PK is the new year, in this case 222 and all other fields are added as well.
FROM Table
WHERE PK = 999;
And I have tried to get something like that (I know that this is not working):
INSERT INTO Table WHERE PK = 222
SELECT * FROM Table WHERE PK = 999;
I hope this is enough information to understand what I would like to achieve and that its possible to do so.
Thanks in advance
If you used tables you don't have to name columns.
So you can build dynamic statements and execute immediate :
declare global temporary table copyrows as (
select * from table1 where pk = 999
) with data with replace;
update session.copyrows set pk = 222;
insert into table1 select * from session.copyrows;