An INSERT statement with SELECT in DB2 and SQL Server
I have a table ITEMS with the following columns:
ID DESCRIPTION
23 ABC
24 DEF
I will like to insert a new record in the table and the next value of ID should be the max +1 (25).
The script will be run in different environments so the last value maybe different.
I will like to create an INSERT statement to be something like this:
INSERT INTO ITEMS (ID, DESCRIPTION)
VALUES (SELECT MAX(ID)+1 FROM ITEMS, ‘GHI’)
The same statement will be executed for a DB2 and SQL Server db.
Is the above statement valid in both db?
ID DESCRIPTION
23 ABC
24 DEF
I will like to insert a new record in the table and the next value of ID should be the max +1 (25).
The script will be run in different environments so the last value maybe different.
I will like to create an INSERT statement to be something like this:
INSERT INTO ITEMS (ID, DESCRIPTION)
VALUES (SELECT MAX(ID)+1 FROM ITEMS, ‘GHI’)
The same statement will be executed for a DB2 and SQL Server db.
Is the above statement valid in both db?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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