Dynamic record duplication in IBM DB2 SQL

user_11520034 0
Viewed 58

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

rpgle db2 copy
1 Answers

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;
user_121118210
Related