An INSERT statement with SELECT in DB2 and SQL Server

Hi,

As Kent mentions above you could use identity column to automatically increase the id column as you insert your data.
Another option is to use sequences, see here  and here for further info.

Both identity columns and sequences are supported in Db2 and SQL Server.
>The script will be run in different environments so the last value maybe different.
Note that having identity columns defined in a table you can do regular and bulk inserts where the dbms takes care of assigning the next value to each row and rollback the values if necessary.
This makes it easy to control and have the id values in sync across different dbms.
Also note that identity columns may produce gaps in certain circumstances.
Using sequences gives the opportunity to have the next value logic in the application code and the same value across multiple tables in the same DBMS. However that makes the application responsible for the rollback logic for the values.

Regards,
   Tomas Helgi

The script I created wasn't properly executed.  I think the keyword VALUE had to be omitted as suggested by Mate.
The idea to alter the column as Identity is very good. I  will also need the default to be the last highest value +1.
I am new to DB2 and sql server. Is it possible to have the precise statement to change existing column to Identity and have default value described above?

Does DB2 and SQL Server have light editions one can install locally to do practice.
Please provide the link.

Thanks

If you just want to test some simple SQL, there's are several pretty good tools available online.

  https://sqltest.net

It let's you build simple tables and query them using quite a few database engines and versions.  That would be easier than installing multiple databases on your desktop to experiment.  :)


Good Luck!
Kent