Direct DB insert to nms:recipient

[nagendrar695179]

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

[Adhiyan]

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

[nagendrar695179]

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]

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]

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