I have a table with an primary key identity column, this table exists on many databases (clients running db2 personal edition). I need to alter the table def, so I want to unload the data, drop the table and recreate it. I need to maintain the key values though for referential integrity.
What I want to do is this:
- CREATE TABLE MyTable_BACKUP LIKE MyTable
- INSERT INTO MyTable_BACKUP SELECT * FROM MyTable
- DROP TABLE MyTable
- <recreate MyTABLE>
- INSERT INTO MyTable SELECT * FROM MyTable_BACKUP
OK, that all works fine.. except the identity start value is now reset back to 1 on the table, so inserting into it inserts a duplicate value.
There is an ALTER TABLE ALTER COLUMN statement for resetting the IDENTITY value, but it only takes a numeric constant, I can't seem to do this:
ALTER TABLE MyTable ALTER COLUMN MyIdentCol RESTART WITH (SELECT MAX(MyIdentCol)+1 FROM MyTable )
Is there a way to reset the next identity value dynamically?
I have about 1000 clients, so I need to be able to do all this within a script I will execute on each client. I'm not familar with creating dynamic SQL statements within a DB2 script.
Can that be done, and if so can you point me in the right direction to get started with it?