2

In the Target table there is a PK column usr_cd which doesn't exist in the Source table. Hence, while inserting from Source to Target I want to generate a unique code. How can I achieve this when I am using MERGE statement?

Following options are tried without luck:

  1. Sequence object: Can't use inside MERGE.
  2. SP call: Can't execute SP inside MERGE in INSERT.
  3. Function call: Won't help as I can't use sequence object inside UDF. Also if I store a unique value in a table, I won't be able to update inside UDF.
  4. Adding code in Source table: Can't be used as some records will be updated and some will be inserted, hence it will break the sequence.

Adding default to Target table is one option but I want to avoid that.

Let me know if there is any other way of achieving this while using MERGE.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
Rajesh Bhat
  • 791
  • 3
  • 8
  • 20

1 Answers1

3

Indeed, the docs for NEXT VALUE FOR say:

Limitations and Restrictions

The NEXT VALUE FOR function cannot be used in the following situations:

In a MERGE statement. (Except when the NEXT VALUE FOR function is used in a default constraint in the target table and default is used in the CREATE statement of the MERGE statement.)

So, if you don't want to use NEXT VALUE FOR as a default constraint in the Target table definition or simple IDENTITY, the only other thing that I can think of is a trigger. An INSTEAD OF INSERT trigger.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • Looks like there is no other option, its better to go for separate update and insert with joins (at least I can use sequence object) rather than merge in this scenario. – Rajesh Bhat Feb 10 '17 at 07:15
  • @RajeshBhat, yes, using separate `INSERT` and `UPDATE` is definitely an option. – Vladimir Baranov Feb 10 '17 at 09:46