We help IT Professionals succeed at work.

An INSERT statement with SELECT in DB2 and SQL Server

56 Views
Last Modified: 2020-06-04
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?
Comment
Watch Question

Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
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
SQL Server Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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




Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.