Creating a SQL insert statement with both parameters and select statement

Question

I am trying to come up with a DB2 SQL statement that does the following, but I am not sure if this is allowed.

I know that it's possible to insert into tableA ( ... ) Values (?,?,?,...)
and then assign values to those parameters ?.

Is it possible to pre-defined the value of one of the parameter?

For example, one of the column that I am trying to insert is the ID column and I would like to make it something like select max(id) + 1 from tableA.

This is what I am trying to get to - is this syntax possible in db2?

insert into tableA (ID, Text1, Text2) VALUES (select max(id)+1 from tableA, ?, ?)

Anyways - any help would be appreciated!

thanks!!

I am curious why you'd not want to use autogenerated primary keys? CREATE TABLE mytable (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ...) — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Apr 18, 2012 at 13:55
I didn't have control on how the table was created, but thanks for the pointer ! — aggietech
– aggietech, Commented Apr 18, 2012 at 13:59

Ahmed Laatabi · Accepted Answer · 2012-04-18 14:06:57Z

4

this should works :

 insert into tableA values((select max(id)+1 from tableA),'text1','text')

answered Apr 18, 2012 at 14:06

Ahmed Laatabi

9371 gold badge8 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

graypay · Accepted Answer · 2012-04-18 14:01:14Z

0

It sounds like you are wanting a primary key as an index on your table.

db2 alter table tableA add primary key (id)

This will create a column in your table which will auto-increment when you add a new record. Source ( http://www.ibm.com/developerworks/data/library/techarticle/dm-0401melnyk/ )

answered Apr 18, 2012 at 14:01

graypay

455 bronze badges

2 Comments

aggietech Over a year ago

Yes - this is one way to go about it to answer my question - but one the other hand is there a way to mix/match values and params?

graypay Over a year ago

In DB2, INSERT can take a VALUES argument OR a SELECT, but not both in one query: publib.boulder.ibm.com/infocenter/iseries/v5r4/…

arserbin3 · Accepted Answer · 2014-09-03 18:05:47Z

0

You can also try using the OVERRIDING USER VALUE parameter:

INSERT INTO TableA
OVERRIDING USER VALUE 
SELECT 0,Text1, Text2
From TableB

edited Sep 3, 2014 at 18:05

arserbin3

6,1688 gold badges39 silver badges53 bronze badges

answered Sep 3, 2014 at 18:01

hms

1

Collectives™ on Stack Overflow

Creating a SQL insert statement with both parameters and select statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related