Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Direct DB insert to nms:recipient

nagendrar695179
Level 2
Level 2

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

4 Replies
Adhiyan
Employee
Employee

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
Level 2
Level 2

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
Level 4
Level 4

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
Community Advisor

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