Direct DB insert to nms:recipient | Community
Skip to main content
A
Anonymous
August 20, 2019

Direct DB insert to nms:recipient

  • August 20, 2019
  • 2 replies
  • 11178 views

In an ongoing engagement, client would like to have a direct backend insert of profile data to recipient. Is this supported in adobe campaign? Would the data reflect in the ACC console? We are working on ACC v7.

Appreciate any help.

Thanks

Ram

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    2 replies

    Adhiyan
    Adobe Employee
    AdhiyanAdobe Employee
    Adobe Employee
    August 20, 2019

    Hello Ram,

    Technically you can insert profile data into recipient table directly in the database and it will be visible in the nms:recipient schema from the console. I tested and found this to be working.

    The main challenge would be though to assign primary Keys for the records you insert as the autopk=true option is not respected when inserting data directly into the DB.

    For example this would work :

    INSERT INTO nmsrecipient (irecipientid,semail,saccount,sfirstName,slastName) VALUES ('1980','adobe@adbc.com','7835845','s','b');

    However this would not work :

    INSERT INTO nmsrecipient (semail,saccount,sfirstName,slastName) VALUES ('adobe@adbc.com','7835845','s','b');

    and fail with error :

    ERROR: duplicate key value violates unique constraint "nmsrecipient_id"

    SQL state: 23505

    Detail: Key (irecipientid)=(0) already exists.

    Hence , to insert data directly , the primary key management needs to be done manually. Otherwise it is technically feasible.

    Regards,

    Adhiyan

      A
      Anonymous
      August 21, 2019

      Hi Adhiyan

      Thank you for the response!.

      If the PK is managed externally, should we set "autopk=false" and let the external system manage the key?

      -Ram

      saikatk2447661
      saikatk2447661
      Level 4
      August 28, 2019

      Hi,

      You can keep the autopk="true" and use the following sql syntax:

      INSERT INTO nmsrecipient (irecipientid,semail,saccount,sfirstName,slastName) VALUES (GetNewId(),'adobe@adbc.com','7835845','s','b');

      Or you can create a custom recipient specific sequence and custom new id function and use that one too as mentioned by Jon.

      Thanks,

      Saikat

      Jonathon_wodnicki
      Community Advisor
      Jonathon_wodnickiCommunity Advisor
      Community Advisor
      August 20, 2019

      Hi,

      Use XtkNewId (or better, create a recipient-specific sequence), and use it for the irecipientid pk when inserting records.

      My current client is doing a variation of this as part of their legacy etl system.

      Thanks,

      -Jon

        Terms & ConditionsAccessibility statement

        Loading footer...