Highlighted

Direct DB insert to nms:recipient

Avatar

Avatar

nagendrar695179

Avatar

nagendrar695179

nagendrar695179

20-08-2019

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

Replies

Avatar

Avatar

Adhiyan

Employee

Avatar

Adhiyan

Employee

Adhiyan
Employee

20-08-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

Highlighted

Avatar

Avatar

wodnicki

MVP

Avatar

wodnicki

MVP

wodnicki
MVP

20-08-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

Highlighted

Avatar

Avatar

nagendrar695179

Avatar

nagendrar695179

nagendrar695179

21-08-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

Highlighted

Avatar

Avatar

saikatk2447661

Avatar

saikatk2447661

saikatk2447661

28-08-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