Re: Identity column re-using existing numbers?

[Arco Simonse <arco400@xxxxxxxxx>]

Thanks for the heads up Dave. I'll enhance our procedure with it!

Regards,
-Arco

Op di 4 dec. 2018 om 20:49 schreef <dlclark@xxxxxxxxxxxxxxxx>:
>         Good idea.  But for my purposes and for performance reasons, there
> is no need to use a cursor -- since you're retrieving a single value.  So,
> I would recode that last part as follows.
>
>
> SET stmttxt = 'VALUES ('
>     || 'SELECT IFNULL(MAX("' || idcolname || '") , 0)'
>     || '  FROM "' || peSchemaName || '"/"' || peTableName || '"'
>     || ') INTO ?';
> PREPARE prepstm FROM stmttxt;
> SET maxid = 0;
> EXECUTE prepstm USING maxid;
> SET stmttxt = 'ALTER'
>     || ' TABLE "' || peSchemaName || '"/"' || peTableName || '"'
>     || ' ALTER COLUMN "' || idcolname || '"'
>     || ' RESTART WITH ' || CHAR(maxid + 1);
> EXECUTE IMMEDIATE stmttxt;
>
>
> Sincerely,
>
> Dave Clark
> --