Avatar of diteps06
diteps06Flag for United States of America asked on

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?
Microsoft SQL ServerDB2SQL

Avatar of undefined
Last Comment
Kent Olsen

8/22/2022 - Mon
SOLUTION
Brian Crowe

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Kent Olsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tomas Helgi Johannsson

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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
diteps06

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




SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kent Olsen

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

Your help has saved me hundreds of hours of internet surfing.
fblack61