0

I'm new to using MERGE statements and GUID.The Insert in the below Statement generates a same GUID (NEWID() generates the same GUID for all the records). Can someone please guide me on how to generate unique GUIDs in this case ?

  MERGE INTO TABLE_A A
    USING (SELECT NEWID() AS SRCID,STU_ID,CAMPUS_ID,DT_ENTRY,SCH_YR,DTS FROM 
  TABLE_B) B ON (B.DTS = A.DLM)
    WHEN MATCHED THEN UPDATE 
            SET A.KEY1 = B.STU_ID
               ,A.KEY2 = B.CAMPUS_ID
               ,A.KEY3 = B.SCH_YR
               ,A.KEY4 = B.DT_ENTRY
               ,A.DLM = B.DTS
               ,A.SOURCE_ID = B.SRCID
               ,A.TYPE = 'TYPE'
    WHEN NOT MATCHED THEN INSERT 
                (A.KEY1
                ,A.KEY2
                ,A.KEY3
                ,A.KEY4
                ,A.DLM
                ,A.SOURCE_ID
                ,A.TYPE) VALUES 
                (B.STU_ID
                ,B.CAMPUS_ID
                ,B.SCH_YR
                ,B.DT_ENTRY
                ,B.DTS
                ,NEWID()
                ,'TYPE')
Cyber_Knight
  • 21
  • 6
  • @KaushikNayak,Below link has details about NEWID() function.. http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1550/html/blocks/CJAFICJI.htm – Cyber_Knight Jul 16 '18 at 05:54
  • adding back oracle and plsql tags as the answer could be used across all the databases.. – Cyber_Knight Jul 16 '18 at 14:10
  • 1
    I am not familiar with NEWID() so that might become available in a newer version of Oracle than I have worked with, which is 11.2. Or it might be something unique to SYBASE? But when I want a unique id in Oracle 11.2, I use SYS_GUID(). – Stilgar Jul 16 '18 at 14:21
  • The `plsql` tag would only be applicable if you want a stored procedure for that –  Jul 16 '18 at 16:45

1 Answers1

1

In the current syntax, the NEWID() BIF call is evaluated once for the query. You can often force evaluation for every row by taking a column and somehow working that into a parameter to the BIF call. NEWID() accepts 0 and 1 as arguments, so you could try something like this -- assuming STU_ID is an integer column:

NEWID(ISNULL(STU_ID,0)*0)

RobV
  • 2,263
  • 1
  • 11
  • 7