CODEWITHSUNDEEP
sqldb2
Ahmad Qasim
Ahmad Qasim

Reputation: 474

Inserting records in one table getting records from 2 different tables

Hey Geeks I am new in DB2 I want to generate surrogates by getting maximum from one table and for that I am doing that 

SELECT    *
FROM      ( SELECT    EMP_NAME ,
                    EMP_ID ,
                    ( ROW_NUMBER() OVER ( ) ) g
          FROM      STG.EMPLOYEE AS A
                    LEFT JOIN PRD.INDIVIDUAL AS B ON A.EMP_ID = B.SRC_KEY
          WHERE     B.SRC_KEY IS NULL
        ) V
        CROSS JOIN ( SELECT ( COALESCE(MAX(INDVL_ID), 0) + 1 ) mm
                     FROM   PRD.INDIVIDUAL
                   ) B;

the above statement is used in insert statement. In the above code I want to maximum which I get from last line.

(select EMP_NAME,EMP_ID,max(INDVL_ID)+(ROW_NUMBER() over())g) from 
 STG.EMPLOYEE)

May You guys got it and thanks in advance

Sample Data is here First table data

STG.EMPLOYEE
EMP_ID|EMP_NAME|  
     3|     def|
     4|     ghi|

Second table data from where i have to get maximum

PRD.INDIVIDUAL

INDVL_ID|INDVL_NAME|SRC_KEY|
       1|       abc|      1|

Output Table

INDVL_ID|INDVL_NAME|SRC_KEY|
       2|       def|      3|
       3|       ghi|      4|

Upvotes: 0

Views: 59

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

Below is an example of allocating surrogate key values based on the current MAX value

INSERT INTO PRD.INDIVIDUAL 
SELECT  ROW_NUMBER() OVER()   
        + (SELECT COALESCE(MAX(INDVL_ID),0) FROM PRD.INDIVIDUAL)  AS INDVL_ID
,       INDVL_NAME
,       SRC_KEY
FROM
    STG.EMPLOYEE

Upvotes: 1

Related Questions