Determine IDENTITY value during multiple INSERTS

Determine IDENTITY value during multiple INSERTS

Post by illfi.. » Thu, 02 Mar 2000 04:00:00



Hello all,

I'm creating a record in two tables that has a foreign key constraint
against an IDENTITY column.  I am trying to determine the value of the
the IDENTITY column in the master table so I can create a record in the
other linked table with the same value.

Currently, I'm using two INSERT statements that add records to the two
tables.  However, for me to add a record to the linked table, I need to
determine the IDENTITY value from the master table and use that value
to add the second record to the linked table.

How can I achieve this?  Please advise.
R. Alcazar

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Determine IDENTITY value during multiple INSERTS

Post by Shawn Clar » Thu, 02 Mar 2000 04:00:00


R. Alcazar,


value of the last identity that was used when inserting a new record into a
table with an identity column.

Shawn Clark
Meridian Technology Group


> Hello all,

> I'm creating a record in two tables that has a foreign key constraint
> against an IDENTITY column.  I am trying to determine the value of the
> the IDENTITY column in the master table so I can create a record in the
> other linked table with the same value.

> Currently, I'm using two INSERT statements that add records to the two
> tables.  However, for me to add a record to the linked table, I need to
> determine the IDENTITY value from the master table and use that value
> to add the second record to the linked table.

> How can I achieve this?  Please advise.
> R. Alcazar

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

1. Determining newly inserted identity value in DB2 v6

In DB2 v6, I have a db2 column that is defined with the identity clause.
Following insert, I need a way to determine the identity value assigned
by DB2.  The SQL Reference book gives the following example, using
VALUES and IDENTITY_VAL_LOCAL:

#1 CREATE TABLE EMPLOYEE
#1 (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
#1 NAME CHAR(3 ),
#1 SALARY DECIMAL(5,2),
#1 DEPTNO SMALLINT);

#2 INSERT INTO EMPLOYEE
#2 (NAME,SALARY,DEPTNO)
#2 VALUES ('Rupert',989.99,5 );

#3 VALUES IDENTITY_VAL_LOCAL()INTO :IVAR;

Now my question is:  
If my batch process is running and has inserted a row (step 2),
then uses the VALUES statement (step 3) to get the value of the
inserted row, however during this time, an online user has
inserted a new row (between step 2 and 3 above), what value will
I get? the value from my insert, or from the online user insert?  
Is this the best way to determine the identity of my inserted row?

Thanks, Dan T

2. Trouble logging into OEM (newbie)

3. Getting the value of an autoincrement or identity field during an sql insert

4. resume of pick d3 / web development expert mcse also

5. Knowing @@IDENTITY value during INSERT???

6. Background Color

7. Getting the value of an autoincrement or identity field during an sql insert

8. Sequential report numbering

9. Retrieve multiple IDENTITY values after batch INSERT?

10. determining the next value of an identity value to be assigned

11. Attempt to insert duplicate identity values in table (has identity and key fields)

12. INSERT INTO with SELECT DISTINCT causes a zero value to be inserted into an IDENTITY column

13. Determining last identity value with DB-Library for C